how to replace data in Azure Sql database using ADF

Question

Hi,

I am using an ADF dataflow pipeline to move data from a Storage resource to a SQL DB, but the data is simply added to the existing table in the database which creates duplicates in the azure SQL database and my SQL memory is now exhausted, However, I want the new data to replace the database entirely without changing the schemas. I have been struggling with this for too long.

Any help?

Answer 1

Hi @Kimp ,

Welcome to Microsoft Q&A Platform. Thank you for posting query here.

You mean to say, you don't want to insert rows in to your sink. You want to update rows in Sink from Source?

If yes, you should consider using Alter row transformation and apply "Update If" Policy on your source rows and inside Sink settings enable Allow updates.

Please Note

• When a row marked as update policy, then that row will only get update in DB. It will not get insert.
• You can also consider marking rows as Upsert Policy. Upsert Policy will inset Row if not present in Sink and update row if already present in Sink.

You can refer video as well to know about alter row transformation.
https://www.youtube.com/watch?v=12Bt9N5lODA

Please check below screenshots where I tried to apply Update policy for all rows from my source.

Source Transformation:

Alter row Transformation:

Sink Transformation

Hope this will help. Please let us know if any further queries.

------------------------------

• Please don't forget to click on or upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how
• Want a reminder to come back and check responses? Here is how to subscribe to a notification
• If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators