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.

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.

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/