ADF - how to compare two Azure SQL Database tables (A and B) with the same structure and to insert only the missing values from table A to table B

mkn 0
Viewed 49

I want to create an ADF data pipeline that compares both tables and after the comparison to add the missing rows from table A to table B

table A - 100 records table B - 90 records

add the difference of 10 rows from table A to table B

This is what I tried:

picture1

picture2 if condition 1 - @greaterOrEquals(activity('GetLastModifiedDate').output.lastModified,adddays(utcnow(),-7)) if condition 2 - @and(equals(item().name,'master_data'),greaterOrEquals(activity('GetLastModifiedDate').output.lastModified,adddays(utcnow(),-7)))

data-pipeline azure-data-factory-pipeline azure-data-factory azure
1 Answers

The Copy activity has an Upsert mode which I think would help here. Simple instructions:

  1. Create one Copy activity
  2. Set your source database in the Source tab of the Copy activity
  3. Set your target (or sink) database in the Sink tab. Set the mode to Upsert
  4. Specify the interim schema. This is used to create a transient table which holds data during the Upsert
  5. Specify the unique keys for the source and target table in the Key columns section so the Upsert can take place successfully

A simple example:

Upsert in ADF Copy activity

Failing that, simply use a Copy activity to land the data into a table in your target database and use a Stored Proc activity to implement your more complicated logic.

wBob - MSFT0
Related