topic Using tDBOutput as a MERGE in Talend Studio

Using tDBOutput as a MERGE

manuk1 — Fri, 15 Nov 2024 23:15:12 GMT

Hi,

In a SQL Server MERGE command, we define 3 things in the UPDATE part :

1) the Keys columns to compare to see if it's an Update or an Insert

2) the columns to compare to know if the Update is necessary

3) the columns that will be updated

Let's imagine that I got the following columns in my Source and Target tables :

Id
Value
Update_DateTime

In my MERGE I can say :

if Target.Id = Source.Id then

if Target.Value <> Source.Value then

Target.Value = Source.Value

Target.Update_DateTime = Source.Update_DateTime

So the Update_DateTime column is not tested in the condition but will be updated if Target.Value <> Source.Value

If I try to use a tDBOutput component to do the same in Talend, I need to specify the Key column (Id) and the tested columns (Value and Update_DateTime) but, in this case, the condition will always be true because the Update_DateTime will always be different. Then all the rows will always be updated, even if the Value is still the same.

Other option, specify the Key column (Id) and the tested columns (Value only) but, in this case, the Update_DateTime column won't be updated if the Value has changed.

I saw that there is a "Use field options" in the Advanced settings of the tDBOutp component but not sure I can use it to solve my problem.

Does someone got an idea for me to solve my issue ?

Not sure if my explanation is clear enough, otherwise don't hesitate to ask.

Re: Using tDBOutput as a MERGE

gjeremy1617088143 — Wed, 02 Feb 2022 15:38:44 GMT

Hi, you can use tSQLTemplateMerge to reproduce SSMS merge:

https://help.talend.com/r/en-US/7.3/sqltemplate/tsqltemplatemerge

Send me love and kudos

Re: Using tDBOutput as a MERGE

manuk1 — Wed, 02 Feb 2022 19:24:16 GMT

Thanks for answering, gjeremy1617088143

I knew the tSQLTemplateMerge component but it seems there are issues sometimes, depending of the Database type.

I also used a tSQLRow to execute a MERGE statement without issues but no statistics about the rows inserted/updated are enable.

That's why I would like to know if a tDBOutput can allow me to simulate a MERGE.

Re: Using tDBOutput as a MERGE

manuk1 — Thu, 03 Feb 2022 07:18:59 GMT

Is it possible to use the "Additional Columns" option, to update my Update_DateTime column ?

Re: Using tDBOutput as a MERGE

gjeremy1617088143 — Thu, 03 Feb 2022 08:24:27 GMT

this link can be helpfull :

https://stackoverflow.com/questions/48110549/get-output-of-tmssqlrow-in-talend

So with tDBRow you have to explicitly return in your query the number of line affected then use a tParseRecorSet to parse the object returned by the tDBRow.

Re: Using tDBOutput as a MERGE

manuk1 — Thu, 03 Feb 2022 13:05:39 GMT

Thanks.

But it's not possible to use this method in my case. With a MERGE command the same instruction will INSERT or UPDATE depending if the ID is still present in the target table. So, I can probably know the total impacted rows but not how many rows were updated and how many rows were inserted.