defining the writer object to write to two MySQL tables sequentially

I am using Advanced ETL Processor Enterprise version 5.7.6.11

I am moving data between MySQL databases. The main problem that I have right now is retrieving the auto-incremented primary key value on the inserted record. I'd like to capture that value in a table called lookupid. The lookupid table will let me map the old (source) primary key value to the new (target) primary key value.

That way, I'll know what value to use in foreign key fields when I start populating the child (dependent) tables.

I thought that I would be able to define a separate insert statement at the writer level to accomplish this. The first/original insert statement would insert data into the target table (slcusertest). The second insert statement would use the last_insert_id() function to capture the newly generated primary key and insert that along with the old primary key into the lookupid table.

It does not work. I only get the inserts into the slcusertest table.

Is there anyway or any other way to get this to work?

I could get this to work if there was a way to perform an insert operation from within a transformer as well as a separate SQL query to the get the last_insert_id() value.

Thanks
Don Martin

You can use just one insert statement here.

The better option is to write stored procedure and map it

Mike

You can also use AETL to generate keys for you, that way is faster

www.etl-tools.com/forum/advanced-etl-pro...es-question?start=12

Peter

Peter,

Since I plan on inserting records into a live production database, I don't that manually generating primary keys is safe to do. I can see where there would be primary key constraint violations. The production database relies upon the auto_increment functionality of the primary key column to generate the keys.

Thanks
Don

Mike,

We are starting a project to move production data between databases. That requires the generation of new primary keys. There are going to do several dozen tables involved in this process. I don't want to write the procedure(s) that it would take to accomplish this.

I kind of like the idea of manually adding a separate insert statement to the writer to capture the last_insert_id value. That would make this project so easy to accomplish Is there any chance of this feature being added sometime?

Thanks
Don Martin

That can be done if you work with Oracle, MS SQL Server, Interbase or PostreSQL.

Because those databases support Anonymous SQL blocks,
But MySQL does not support it so you have to use stored procedures

But there is better option you can just load data into auto inc field.

Have a look at this
forums.mysql.com/read.php?10,167702

Mike