Is there a simple way to sync two tables while ignoring rows that are equal?

nsavich — Fri, 15 Nov 2024 23:53:34 GMT

Hi,

I am very new to Talend, and realize that this might be a rather ignorant question, but I am curious whether this scenario is possible as a function of the tMap component.

I have two tables - a source and a target. I want to keep the target table updated via the source table, but I want to preserve the rows in the target table that have not changed. This seems to be a pretty typical ETL scenario, but I am not finding a simple method of enforcing this in Talend - insert rows that are missing in the target. Update rows that have changed in the source for the given primary key.

I was told to use tDBInput tMap and tDBOutput, defining the schema for both the input and output and mapping the columns via tMap in addition to defining a primary key in both the source and target tables. I am 100% confident that my schemas are defined correctly. Fields are mapped accurately. Data types are the same. The tDBOutput "Action on Data" value is set to "Insert or Update". However, all rows are being updated regardless of whether there is a difference between the source and target. My assumption was that Talend would use the primary keys to determine whether to insert a row, or otherwise evaluate the remaining columns to determine if any values were different between the source and target as related by the primary key join.

Bad assumption on my part?

I could always develop a different solution using a stored procedure or some other method. It's not that I am looking for an easy solution, but I don't want to over engineer a simple process either.

Thanks,

Nick

Re: Is there a simple way to sync two tables while ignoring rows that are equal?

Anonymous — Thu, 05 Aug 2021 09:50:36 GMT

You are right, Talend use the primay keys to determine whether to insert a row or update a row. If the primary keys already exist, it will update the remining columns regardless of whether they are the same.

If you want the ignore the rows that are equals, you need to do a join between source table and target table based on all columns using tMap to get the identical rows, the performance is bad if the amount of data is large.

Regards

Shong

Re: Is there a simple way to sync two tables while ignoring rows that are equal?

nsavich — Thu, 05 Aug 2021 11:43:41 GMT

Thanks. I may just do the work in the database and transfer the results to the target table.

topic Re: Is there a simple way to sync two tables while ignoring rows that are equal? in Talend Studio

Is there a simple way to sync two tables while ignoring rows that are equal?

Re: Is there a simple way to sync two tables while ignoring rows that are equal?

Re: Is there a simple way to sync two tables while ignoring rows that are equal?