topic Re: Usage of tDBOutput component for insert and update scenario in Talend Studio

Usage of tDBOutput component for insert and update scenario

DBS1 — Tue, 05 Mar 2024 06:13:20 GMT

Hi team

I have a query regarding the usage of tDBOutput component in Talend.

I have a loadable file which is read using tFileInputDelimited and is connected to tDBOutput. In tDBOutput, for "Action on Data" I have selected as "Insert or Update" as I need to insert or update based on the key.

In table, I have two columns which is created_by and updated_by. During insertion, I have to update both these columns by jobName whereas at the time of updation, I don't want to touch the created_by column. How can this be accomplished.

Thanks

Dhanoop B S

Re: Usage of tDBOutput component for insert and update scenario

Shicong_Hong — Tue, 05 Mar 2024 09:37:03 GMT

Before inserting or updating the data into table, you need to read the data from file and then do an inner join with the key columns from table on tMap to get the data which will be inserted and other data will be updated, use different tDBoutput components to insert data and update data respectively.

Add a tMap before tDBOutput,

For inserted data, add extra columns created_by and updated_by in the output table of tMap, and set the expression of created_by and updated_by column as jobName.

For updated data, only add extra column updated_by in the output table of tMap, and set the expression of updated_by column as jobName.

//jobName is a global variable that stores the job name.

Regards

Shicong

Re: Usage of tDBOutput component for insert and update scenario

DBS1 — Tue, 05 Mar 2024 09:45:13 GMT

Hi @Shicong_Hong

Thanks for the response.

I have only implemented like what you have mentioned below. But this was taking time as we need to load all data from table to perform the inner join operation.

I would like to know if there is any solution, where Talend itself will determine which rows needs to be inserted/updated based on primary key and have different schemas for insert and update scenario.

Thanks

Dhanoop B S

Re: Usage of tDBOutput component for insert and update scenario

Shicong_Hong — Tue, 05 Mar 2024 09:56:50 GMT

Unfortunately, the job can't get which rows have been inserted/updated through tDBOutput component. We have to select the rows before insertion/update for more processing.