Question 264362 in Azure Question posted in
The official documentation to get you started can be found here.

Azure – ADF "Copy data" activity – Can I delete DB data as well as upsert?

I want to copy an Azure SQL DB table exactly from a source DB to a destination DB. I currently have a "Copy data" activity that works great for upserting the data. But I also want to delete any records from the destination that do not exist in the source.

Truncating and recreating the destination table is not an option (lots of FKs). The only workaround I can think of is to create a staging table in the destination DB, dump all the source records to it, delete from destination if not exists in staging, and then drop the staging table. Is there a more elegant way of doing this? I’m surprised this isn’t just a checkbox somewhere in the Copy data activity!

Tags:

2

Answers


  1. 0

    below would be the best way with a quick performance :

    1. copy all tables from source into staging tables in sink (via either truncate insert or drop and auto create)
    2. write a stored procedure wherein you would truncate table in the reverse order of FK key aspects and populate the data from staging into final tables based on reverse order all within a transaction.

    unfortunately that is the way and there is no aspect of delete which is not present in source but in sink with upsert option because:

    1. in case if you receive only delta updates from source on daily basis then the records in sink tables might be deleted. So in case of upsert , there is no deletion in sink as of now
    Login or Signup to reply.
  2. 0

    If you want to delete the records from sink which are not in source, you can follow below options:

    1. If you want to remove data from a sink (let’s say sink), you may need to load the previous source data into a temp table in the sink database, write a stored procedure to remove and upsert the data from temp to sink and call it from stored procedure activity after first copy activity that copies data from source to temp table.
    2. If you are allowed to use data flow then, in order to create the condition to delete data from the sink, you may alternatively add two sources (source table and sink table) and use Alter row transformation on top of the sink table in Mapping dataflow.
      enter image description here
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search