topic Re: Inserting into MySql parent/child tables in Talend Studio

Inserting into MySql parent/child tables

Anonymous — Fri, 11 May 2018 11:36:36 GMT

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.

Thanks,

Andy

Re: Inserting into MySql parent/child tables

Anonymous — Tue, 22 May 2018 09:17:34 GMT

Hello,

Here is a scenario about:TalendHelpCenter: Inserting data in mother/daughter tables.

Let us know if it is what you are looking for.

Best regards

Re: Inserting into MySql parent/child tables

Anonymous — Tue, 22 May 2018 10:26:04 GMT

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.