increament load based on my status column

Question

Hi Team,
Hi How increment load based on my Status column using Azure Datafactory

Below table is my query feching the records (Source)

based on my status values
if status is Insert then insert the records in my destination table
if status is Update then update the records in my destination table
if status is Delete then deletethe records in my destination table

please any body help me how load based on status
Thanks
Shanvitha

Answer 1

Hello @Shanvitha ,

Thanks for the ask and also using the Microsoft Q&A.

I am assuming that your sink is also table . I think you can use Mapping data flow ( MDF ) . You should use
the Alter row feature . The below gif is self explanatory , but i just wanted to empahasize the expression which
you will have to use in ALTER ROW .

Status=="DELETE"
AND
Status=="UPDATE"
AND
Status=="INSERT"

I just assumed the key column to invoice_id .

I did tested with the data which you provided .

create table fooSourceTable   
(   
 Name varchar(100)  
 ,dep varchar(100)   
 ,Invoice_id int   
 ,Status varchar (100)   
)   
INSERT INTO fooSourceTable values ('A','1A',1,'INSERT')  
INSERT INTO fooSourceTable values ('B','3C',3,'INSERT')  
INSERT INTO fooSourceTable values ('C','1A',5,'UPDATE')  
INSERT INTO fooSourceTable values ('D','2C',7,'UPDATE')  
INSERT INTO fooSourceTable values ('E','4A',12,'DELETE')  

I had the same data in the destination table after the pipeline run , the one row was deleted 2 new rows inserted .

Thank you
Thanks Himanshu

Please do consider to click on "Accept Answer" and "Upvote" on the post that helps you, as it can be beneficial to other community members.