dealing with MySQL auto increment fields to maintain parent child data relationships on insert.

Hi

We recently installed Advanced ETL Processor Ent version 5.7.2.0.

We are starting a project to move subsets of production data into test databases. The database that we use is MySQL. The primary key on each table is generated by the auto increment property on each table. Therefore, we don't know before performing an insert what the primary key will be. (When we did this kind of thing with Oracle, we could always capture the primary key before the insert by querying the sequence associated with the primary key).

I need to know what the primary key is afterwards so I can use it in the foreign key fields of the associated child records.

There is a section in your documentation that discusses how to generate a primary key but that does not pertain in this instance. (Section 10.21 Generating Primary Keys and Defaults).

I see that I can run a SQL statement from the writer after a write operation. Hopefully, I would be able to use this to capture the primary key of a newly inserted record in a lookup. The SQL statement would be a call to the MySQL function LAST_INSERT_ID().

Do you have any examples or guidance on how to do this?

Thanks

I need to know what the primary key is afterwards so I can use it in the foreign key fields of the associated child records.

Does it mean that you want to get this id after every insert or once all data is loaded?

Mike

Mike,

Either way would work. I need to somehow generate a map of old primary keys to new primary keys by table name. I was thinking that it would be easier and more reliable (but likely to be slower) to capture the new primary key after each and every insert. That would mean that the array size specified on the writer would be 1.

Since the target tables reside in a QA/Test database, they cannot be modified. Otherwise, it would be simple to add another field to the target table to hold the old primary key value.

I am open for any ideas.

Thanks

I was thinking that it would be easier and more reliable (but likely to be slower) to capture the new primary key after each and every insert. That would mean that the array size specified on the writer would be 1.

That would be so slow so it will make transformation useless.

You can use system variable to catch last value

1. create new sql script
2. type select max(id_field) from table_name
3. create new package add transformation into it
4. Add SQL Data check and use sql script a the step number 1 as source for it
5. after execution of SQL Data check the value will be stored in

Mike