How to use Copy activity for Daily Incrementals using Merge/Upsert statement

Question

How to copy incremental 100k plus rows each day using Copy Activity.

Source: SQL Server
Sink/Target: Azure SQL Database

Copy Activity SQL Server Source: Select * from source_tablename where [Last LoadDate] < [source_watermark date]
Copy Activity Azure SQL Sink/Target: Can't see option to use Merge Stored Procedure. Only have option to insert to target table. Can't find update option, if record exists.

I saw the link to use table type for each table, which is cumbersome if I have 100 tables. Also, I think table type might result with performance issues, if I have daily incremental above 100K for few tables. (https://learn.microsoft.com/en-us/azure/data-factory/tutorial-incremental-copy-multiple-tables-portal)

Note: I don't have option to use data flows as it is not supported to copy from on-premises source data and can't re-write the logic from existing Stored Procedure to built-in data flows functions.

Please suggest simple options to copy from on-premises to Azure SQL using Stored procedure merge.

Answer 1

One option is to use Copy activity that copies data from Source to target staging table. Later run SP using Stored procedure activity to merge the data from staging to main table.

Somewhat similar tutorial is available on Microsoft Learn - https://learn.microsoft.com/en-us/azure/data-factory/tutorial-incremental-copy-multiple-tables-portal

----------

Please don't forget to Accept Answer and Up-vote if the response helped -- Vaibhav

Answer 2

Thanks Vaibhav,

The link you shared is for creating Table Type. In my case, since the incremental records are high (few tables ranging from 100k-200K records daily), therefore I suspect Table Type would downgrade the copy performance, please confirm if this is not the case ? Second, table type is additional layer which need to be updated with any changes in the target schema.

Also, I would like the solution which can give good performance for first time Initial Data Load.

Just wanted to know alternative solution instead of Table Type and data flows.