One Star

Migrating data in tables with foreign keys and auto-increment

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.
2 REPLIES
Moderator

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

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
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Seven Stars

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

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 http://www.talendforge.org/forum/viewtopic.php?id=30858.