MERGE data in a Dataflow of Azure Data Factory into an existing table

Question

I am trying to create a DataFlow under Azure Data Factory that inserts & updates rows into a table after performing some transformations. When I am trying to write the modified data into a 'Sink' I am selecting both checkboxes, 'Allow Inserts' & 'Allow Updates'. A message pops up telling me to create 'Add Alter Row'. What I want to do is simply update if the primary columns match; otherwise insert rows but I cannot figure out how to do that under 'Add Alter Row'.

To summarize, I want to write all the rows back to the table. If PK columns match then update row; otherwise insert row. How do I do that? Unfortunately, truncating the table is not a solution I can use.

Essentially, I need to perform a 'MERGE'.

Answer 1

I solve it and it works. I will merge my two tables TEST2 and TEST3, all the rows will write to TEST3.

Data in my table TEST2 and TEST3:

Here's my Data FLOW:

Firstly, using JOIN to get the data from TEST2 and TEST3:

Alter Row settings and Data Preview:

Sink settings and Data Preview:

Check the data in TEST3:

Hope this helps.