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

Azure – How to update SQL Server table from ADF?

I need to update the Azure SQL Server table if another SQL Server tables is updated.

For example: I have one Azure SQL table dbo.Azuretable and I also have a SQL Server table dbo.sqlservertable. Both these tables are part of two different SQL Server instances, but the table schema is exactly the same.

What happens is dbo.sqlservertable updates on daily basis, let’s say every day one row inserts in dbo.sqlservertable, I want the same row to be inserted into dbo.Azuretable as soon as dbo.sqlservertable gets updated. I want to do it Azure if possible or I am open to any other method as well.

What I have done so far:

I created one copy activity which replicates the changes to dbo.Azuretable what ever happened in dbo.sqlservertable. But I need to manually trigger it because I don’t know when my dbo.sqlservertable gets updated. I want to automate this process.

Tags:

2

Answers


  1. 0

    There are multiple ways to sync the data :

    1. use data sync in azure sql database to sync from sql server to Azure SQL database
      https://learn.microsoft.com/en-us/azure/azure-sql/database/sql-data-sync-data-sql-server-sql-database?view=azuresql

    2. You can create a trigger on the table in SQL server 2017 on insert,update,delete

    and via trigger, call a stored procedure which would export data into blob via polybase.
    leverage blob trigger in adf pipeline to sync data from sql server to Azure SQL database

    https://learn.microsoft.com/en-us/sql/relational-databases/polybase/polybase-versioned-feature-summary?view=sql-server-ver16

    Else you can also trigger a SQL server job wherein you can have a powershell logic to generate a file in blob or trigger ADF REST API for pipeline trigger

    sample reference:
    https://datasharkx.wordpress.com/2022/10/01/event-trigger-data-sync-from-sql-server-to-synapse-via-azure-data-factory-synapse-pipeline/

    Login or Signup to reply.
  2. 0

    First, you require to find inserted rows. Use except functions.

    select * from dbo.sqlservertable
except
select * from dbo.Azuretable

    Then add to "dbo.Azuretable"

    WITH new_records AS(
    select * from dbo.sqlservertable
    except
    select * from dbo.Azuretable
)

INSERT INTO dbo.Azuretable
SELECT * FROM new_records

    You can use data factory or synapse pipeline for it. Pipeline->Activities->General-> Script meets your demand. After that you’ll see lightning icon names add trigger, click and new/edit. Bring your mouse to choose trigger and click. Now, click new. There are all schedule settings. I hope this solution helps you.

    enter image description here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search