topic Re: Migrating data in tables with foreign keys and auto-increment in Talend Studio

Migrating data in tables with foreign keys and auto-increment

_AnonymousUser — Wed, 11 Sep 2013 10:34:58 GMT

We are going to migrate data from MS Access to MySQL.
The MS Acess table schema:
Table User
ID Name Sex
Table Record
ID MAINID Seq APPID Name
Table Application
ID Name
Record.MAINID is the foreign key to User.ID
Record.APPID is the foreign key to Application.ID
The MySQL table schema:
Table User
keyid name sex
(autoincrement)
Table Record
keyid userid applicationid name
(autoincrement)

Table Application
keyid name
(autoincrement)
Record.userid is the foreign key to User.keyid
Record.applicationid is the foreign key to Application.keyid
I create 3 FileInputs and 3 MySqlOutputs to migrate data to table User, Application and Record, one by one, by trigger onSubjobOK. But when migrating the data in Table Record, I cannot find the autoincrement keyid values in Table Application and User to map to userid and applicationid fields in Table Record.

Re: Migrating data in tables with foreign keys and auto-increment

Anonymous — Thu, 12 Sep 2013 07:58:58 GMT

Hi,

I create 3 FileInputs and 3 MySqlOutputs to migrate data to table User, Application and Record, one by one, by trigger onSubjobOK. But when migrating the data in Table Record, I cannot find the autoincrement keyid values in Table Application and User to map to userid and applicationid fields in Table Record.

For auto-increment id, you can set Var in tMap to get a Numeric.sequence.
Best regards
Sabrina

Re: Migrating data in tables with foreign keys and auto-increment

alevy — Thu, 12 Sep 2013 15:41:13 GMT

Easiest way is to load the User and Application tables and then read them back as lookups to tMap to load the Record table. Otherwise, see https://community.talend.com/t5/Design-and-Development/MySQL-Childen-insert-after-Parent-insert-with-parent-sequence/td-p/114799.