You are not logged in.

#1 2011-11-16 22:45:54

gddc
Member
5 posts

Migrating Multiple Related Tables

I'm not sure if what I am attempting to do is possible, but it certainly feels like it should be, I'm just not quite there.

I have two tables (actually, I have several hundred, but if I can get this concept working in my head I can make the others manageable).  One contains Companies.  The other contains Contacts.  A Company has a one to many relationship to Contacts.  I need to migrate them to a new database, but maintain their relationships.  So far I have these components:

1.  MySql Destination DB Connection
2.  MSSQLDBInput - Source Table.
3.  tMap - Successfully maps columns from Source to Destination.
4.  tMysqlOutput - Destination Table.
5.  tMysqlLastInsertID - Retrieves newly generated Insert ID for each company.

I'm not sure how to proceed past this point.  I need to use the Company_ID that existed in the MSSQL Database to query the Contacts Table for Contacts related to that Company, then insert them into the new Contacts Table using the new Company_ID that was generated when inserted the Company into the MySql Database.  I've attached a screen shot of what I have so far (it's not much).  Thanks in advance for any help / suggestions.

Offline

#2 2011-11-17 08:32:18

mpa
Member
257 posts

Re: Migrating Multiple Related Tables

gddc,

I'm guessing u could make a join between the two company tables over the company name field or any field that would be unique in the table. Then have like 2 columns: MssqlCompanyID and MySqlCompanyID.
Then make a join with your contact table over the MssqlCompanyID and use the new ID in mysql: MySqlCompanyID.

Regards

Offline

#3 2011-11-17 09:51:44

archenroot
Member
169 posts

Re: Migrating Multiple Related Tables

Hi,

I don't understand the reason for replacing the key for Company records, if the structure in old database is ok, I would rather do "dummy" transfer of source tables to destination. The old key is still unique, so you could insert it into new table.

On the other hand, if you have defined sequence to generate keys for table Company, approach mentioned above could throw you in trouble in future.

Ok, your idea is not difficult to implement:
As soon as old and new Company keys are known at different places in your job, I would store these keys in global or context variables. After the both keys are stored you can use tFlowToIterate component, this will allow you to start actions for each transfered record of Company. After tFlowToIterate you schould transfer tMSSqlInput also the contacts with SQL statement like:

"SELECT * from Contacts WHERE Company_ID = " + context.OldCompanyID

and finally use tMySQLRow to update newly inserted records in way like:

"UPDATE Contacts SET CompanyID = " + context.NewCompanyID + " WHERE CompanyID = " + context.OldCompanyID

Ladislav


Emperor wants to control outer space Yoda wants to explore inner space that's the fundamental difference between good and bad sides of the Force

Offline

#4 2011-11-17 17:06:01

gddc
Member
5 posts

Re: Migrating Multiple Related Tables

Thanks for the replies. 

The old database used an algorithmically generated binary byte structure as the primary key, making it nigh impossible to transition directly into the new database.  We need to maintain the association between the Companies and Contacts, but want to move to a more reasonable key generation approach. 

I'll look into using the context variables to store the information.  That will probably do what I need it to.  It also looks like I need to review the information for the tFlowToIterate component - it is probably the piece I'm missing that will bring everything together.

Thanks again, and I'll report back with my results or any other questions I encounter.

--Dan

Offline

#5 2011-11-18 21:33:40

gddc
Member
5 posts

Re: Migrating Multiple Related Tables

Alright, I'm making significant headway on this project.  The tFlowToIterate seems to have helped clear things up in my head immensely.

The next thing I'm having trouble with is retrieving a key value from the first insert for use in the second insert.  It seems like MysqlLastInsertId only returns the value if it's an Auto_Increment column.  Unfortunately, the Keys that are being generated in our new database are UUIDs.  Is there a convenient trick for getting those back after insert in MySql, or am I going to have to manually try to query that ID Column out after the insert?  I'm already working on implementing the second approach, but if there's a nifty trick for the first I'd take it.  Thanks for all the help so far, and for any forthcoming advice!

--Dan

Offline

#6 2011-11-21 14:11:18

archenroot
Member
169 posts

Re: Migrating Multiple Related Tables

Well, you can calculate the key for yourself. if you tak a look into sequence on db side, you initiate context variable for sequence + 1 and for each row, you could just incement it by 1.


Emperor wants to control outer space Yoda wants to explore inner space that's the fundamental difference between good and bad sides of the Force

Offline

#7 2011-11-21 16:22:44

gddc
Member
5 posts

Re: Migrating Multiple Related Tables

I think I've got it figured out.  I ended up using the Additional Columns section in Advanced Settings for the MySql Output Component.  It allows me to make a call to UUID() on the MySql database to generate the ID values as necessary.  Looks like I'm good to go - now I just have to work through all of the tables and make sure I create the necessary associations.  Thanks a ton for all of your help.

Offline

Board footer

Talend Contributor Agreement - Talend Website Privacy Policy