With the "upsert option" , should I expect to see "0" as "Rows Written" in a copy activity result summary? My situation is this: The source and sink table columns are not exactly the same but the Key columns to tell it how to know the write behavior are correct. I have tested and made sure that it does actually do insert or update based on the data I give to it BUT what I don't understand is if I make ZERO changes and just keep running the pipeline , why does it not show "zero" in the Rows Written summary?

CodePudding user response：

The main reason why rowsWritten is shown as 0 even when the source and destination have same data is:

• Upsert inserts data when a key column value is absent in target table and updates the values of other rows whenever the key column is found in target table.
• Hence, it is modifying all records irrespective of the changes in data. As in SQL Merge, there is no way to tell copy activity that if an entire row already exists in target table, then ignore that case.
• So, even when key_column matches, it is going to update the values for rest of the columns and hence counted as row written. The following is an example of 2 cases

The rows of source and sink are same:

• The rows present:

id,gname
1,Ana
2,Ceb
3,Topias
4,Jerax
6,Miracle

When inserting completely new rows:

• The rows present in source are (where sink data is as above):

id,gname
8,Sumail
9,ATF