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

Question

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)))

Source destination is mySQL but the sink is Azure SQL DB and the data comparison is between two Azure SQL server tables in a same database — mkn, Sep 14 '22 at 08:37
Have you looked at using the Copy activity Upsert mode? It could probably help here. — wBob, Sep 14 '22 at 08:47

score 0 · Answer 1 · answered Sep 14 '22 at 08:54

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

Create one Copy activity
Set your source database in the Source tab of the Copy activity
Set your target (or sink) database in the Sink tab. Set the mode to Upsert
Specify the interim schema. This is used to create a transient table which holds data during the Upsert
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:

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.

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

table A - 100 records table B - 90 records

1 Answers1