Usage of tDBOutput component for insert and update scenario

[DBS1]

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

[Shicong_Hong]

Hi

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

[DBS1]

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

[Shicong_Hong]

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.