Azure Data Factory - MERGE table1 when matched update selected columns else insert all columns

Question

How to perform following (selected column update) in Azure Data Factory (Data Flow) without using a Stored Procedure (T-SQL):

MERGE Journal_table AS Target
USING data AS Source
ON (Target.load_date = Source.load_date and Target.name = Source.name )
WHEN MATCHED THEN
UPDATE SET
country = Source.country
, state = Source.state
, update_ts = CURRENT_TIMESTAMP
WHEN NOT MATCHED THEN
INSERT (
load_date
, name
, country
, state
, address
, house_no
, date_dispatched
, intake
, insert_date
, update_date
) VALUES (
Source.load_date
, Source.name
, Source.country
, Source.state
, Source.address
, Source.house_no
, Source.date_dispatched
, Source.intake
, CURRENT_TIMESTAMP
, CURRENT_TIMESTAMP
);
END;

Answer 1

Hi @Rejeev Kumar ,

Welcome to Microsoft Q&A Platform. Thank you for posting query here.

Did you tried using alter row transformation for this requirement? Kindly see if that helps and let us know updates.

Alter Row transformation can be used to set insert, delete, update, and upsert policies on rows. You can add one-to-many conditions as expressions.