I need to migrate a lot of data from an old "flat-file" style database into a more modern structured database. A specific example is where I have a source table for an organisation that has details of two telephone contacts in two sets of columns. The target database uses an organisation parent table and a organisation_contact child table. The organisation_contact table has a foreign key column that relates to the organisation.
I can populate the organisation table and I can obtain the last insert id. The attached image shows the project layout.
row4 contains the organisation id and the columns inserted into the organisation table.
row1 contains the telephone contact data for each organisation. The telephone data is filtered out by tMap_1 because it doesn't belong in the organisation table.
I can't figure out how to bring these together to write the organisation_contact table with 0, 1 or 2 rows depending on the data in the input schema.
Here is a scenario about:TalendHelpCenter: Inserting data in mother/daughter tables.
Let us know if it is what you are looking for.
Thank you for your response. This example does go some way to achieving what I am after but I can't see how to create more than one entry in the daughter table for a single entry in the mother table. If I use more than one daughter schema I am unsure how you would get the primary key of the mother table to daughter records after the first. To be clear I want something like this very simplified example:
Name Telephone1 Telephone2 Fred 01752123456 07813925637 Bill NULL 07813765642 Eric NULL NULL ID Name 1 Fred 2 Bill 3 Eric PID Telephone 1 01752123456 1 07813925637 2 07813765642
This shows the input, the output parent and the output daughter table. Note that the Parent ID (PID) = 1 appears twice in the daughter table.