Using tDBOutput as a MERGE

[manuk1]

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.

[gjeremy1617088143]

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

[manuk1]

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.

[manuk1]

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

[gjeremy1617088143]

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.

[manuk1]

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.