Alter Row transformation for implementing upsert

Question

I intend to utilize the Alter Row transformation for implementing upsert functionality. In my target table, there are two columns, InsertedTime and UpdatedTime. For a new record insertion, I want to update both columns with the current datetime, and for an update, I only want to update the UpdatedTime column. I'm currently using Alter Row for both Insert and Update operations. How can I achieve the following:

If it's an Insert operation, set values for both InsertedTime and UpdatedTime columns.

If it's an Update operation, set values only for the UpdatedTime column.

Answer 1

Hi @Lavanya Bhajanthri

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

Did you try using the Alter row for any of the Insert and Update Conditions yet?

I have added the ADF dataflow with transformations to help with the initial query!

Step1:
Use Join for Source and Target Tables using Left Outer Join

Use Select Transformation to avoid column names confusion!

Use Derived Column transformation to create 2 new columns:

InsertOrUpdate = iif(isNull(Trg_ID), 'Insert', 'Update')
NewValue = Src_Value

Step2:

Use Split Condition: For Update and Insert

Update Path:

*
Use below condition ,It is for 2 paths (Update & Insert)
*

Add Derived Column:

Add Alter Row:

SINK: Select "Allow Update" With Key Column as 'ID'

Mapping: update path Sink

Insert Path:

Add Derived Column to the Insert condition.

2 new Columns to capture CurrentUTC(), or whatever the time format you want!!!

Add Alter Row:

Add SINK: Select "Allow Insert" method

Mapping SINK for the Insert path.

SQL Server Tables:

Before :

After the ADF pipeline run:

Hope this helps. Do let us know if you any further queries.

---

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.