updating a value in sink table -azure data factory

Alfaisal Albakri 0
Viewed 53

I'm trying to move data from Mariadb to Azure SQL database lets assume that the table in the Azure SQL contains the following data :

id state insert time updated value
1 100 2022-01-01 14:30:00 yes
2 100 2022-01-01 14:30:00 yes
10 200 2022-01-01 14:30:00 yes

Lets assume I retrieved data from the marinade and it returned the following results :

id state insert time updated value
1 300 2022-01-01 15:30:00 yes
2 200 2022-01-01 15:30:00 yes
12 100 2022-01-01 15:30:00 yes

What I want to do is to check if the query results from the Maria returns ids that are exists in the azure SQL if it exists it updates the value of "updated value" column in the azure SQL to No then insert the query results of the Mariadb so the table should look like this :

id state insert time updated value
1 100 2022-01-01 14:30:00 NO
2 100 2022-01-01 14:30:00 NO
10 200 2022-01-01 14:30:00 yes
1 300 2022-01-01 15:30:00 yes
2 200 2022-01-01 15:30:00 yes
12 100 2022-01-01 15:30:00 yes

Most of the things I found on the internet are performing upset but I want to keep the old data in order to have insights on the states per a given time . So how can I implement such a thing

azure-data-factory-pipeline azure-data-factory mysql sql-server azure
1 Answers

After you insert the new data you should be able to update the existing records with something like this (not tested) assuming you have the new 'insert time'.

update yourTable 
set [updated value] = 'NO' 
where id in (select id from yourTable where [insert time] = @NewInsertTIme)

where &NewInsertTime is the new 'insert time' (2022-01-01 15:30:00 in your example). You can use the Script Activity to run the code or put it in a stored procedure and use the Stored Procedure Activity to call it.

Scott Mildenberger0
Related