how to replace data in Azure Sql database using ADF

Kimp 1 Reputation point
2021-10-06T09:01:52.81+00:00

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?

Azure SQL Database
Azure Blob Storage
Azure Blob Storage
An Azure service that stores unstructured data in the cloud as blobs.
2,610 questions
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
10,111 questions
{count} votes

1 answer

Sort by: Most helpful
  1. ShaikMaheer-MSFT 38,326 Reputation points Microsoft Employee
    2021-10-07T06:38:58.527+00:00

    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:

    138357-image.png

    Alter row Transformation:

    138423-image.png

    Sink Transformation

    138358-image.png

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

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

    • Please don't forget to click on 130616-image.png or upvote 130671-image.png 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
    0 comments No comments