topic Re: How to insert data from CSV file into Parent and Child tables with Child table having the Parent table id as foreign key? in Talend Studio

How to insert data from CSV file into Parent and Child tables with Child table having the Parent table id as foreign key?

GRana1604394240 — Sat, 16 Nov 2024 01:10:52 GMT

I have a CSV file with some client data. The format is as below :

Client_Name;Time_Sheet_ID;Business_Unit;Location;Cost_Code;Worker_Name;Payroll_Number;Time_Sheet_Start_Date;Time_Sheet_End_Date;Time_Sheet_Submit_Date;Time_Sheet_Approved_Date;Rate_Code;Submitted_Units;Pay_rate;Bill_rate

XYZ;XYZTS00000123;XYZ Client;<xyz>;XYZNAME, ;XYZREF;31-08-2020;06-09-2020;08-09-2020;08-09-2020;XYZ/Hours;3.0;28.0;30.0

I have two tables :

Parent - It has following columns :

id - Primary Key

Client_Name

Time_Sheet_ID

Business_Unit

Location

Cost_Code

Worker_Name

Payroll_Number

Time_Sheet_Start_Date

Time_Sheet_End_Date

Time_Sheet_Submit_Date

Time_Sheet_Approved_Date

I want all the columns till 'Time_Sheet_Approved_Date' in the CSV above to be mapped with Parent table.

Another table is :

Child - It has following columns :

parent_id - Parent's table id.

Rate_Code

Submitted_Units

Pay_rate

Bill_rate

The columns from '

Rate_Code

' till '

Bill_rate

' in the above CSV are mapped with this table.

Now I want a mapping such that the CSV data can be inserted into the Parent table and then to the Child table along with the appropriate Parent_id.

I tried with Last_Inserted_Id but it only works if the Child table doesn't have its own Auto Generated Id.

How can I implement this use case in Talend? I am using version 7.2.1.

Thanks,

Gaurav

Re: How to insert data from CSV file into Parent and Child tables with Child table having the Parent table id as foreign key?

manodwhb — Tue, 03 Nov 2020 11:20:05 GMT

@Gaurav Rana , you need to have two flows in a job.

1) read a file and load to parent tables

2) you need to take on subjob ok from first flow and then do lookup with file and parent table to get child job.

as part of generation of primary key ,you need to use Numeric.sequence("Parameter name", start value, increment value)

Thanks,

Manohar

Re: How to insert data from CSV file into Parent and Child tables with Child table having the Parent table id as foreign key?

GRana1604394240 — Wed, 04 Nov 2020 07:29:38 GMT

Thanks Manohar for the prompt response.

However, it would be more understandable if you can provide with an example by creating a sample job. 🙂

Thanks,

Gaurav.

Re: How to insert data from CSV file into Parent and Child tables with Child table having the Parent table id as foreign key?

GRana1604394240 — Wed, 04 Nov 2020 07:35:58 GMT

Hi Manohar,

I need to insert the Parent's table Primary Key (Auto generated ID) into the Child table.

Is there a way to implement this such that a row is read from the source CSV file and an entry with the mapped columns is made in the Parent table.

Now, when the data is inserted in Parent table, we will have the Primary Key (Parent's table Id).

I now need to make an entry in the Child table with the remaining mapped columns of the same input row along with the Parent's table ID generated in the above step.

Also, in your reply, the primary key that I need to use Numeric.sequence for is the Parent's table or Child's table ? And what significance does it have in this use case?

Thanks,

Gaurav.