Data was not updating rows that contained existing primary key

Question

Data was not updating rows that contained existing primary key

SPorter 0

Problem:

Data was not updating rows that contained existing primary key.

Solution:

Go through and run where row exists update if not insert.

Conflict:

This increases row count which thus increases total files: 100+ added to each month.

Possible Resolution:

Is it possible to utilize azure data factory to create a procedure where data can update/ insert rows? If so how?

VRISHABHANATH PATIL 1,820 Reputation points Microsoft External Staff Moderator

2025-11-11T06:31:42.2+00:00

Hi SPorter,

I hope you had a chance to review the information shared earlier, and I hope this information has been helpful! If you still have questions, please let us know what is needed in the comments so the question can be answered.

2 answers

Your answer

VRISHABHANATH PATIL 1,820 Reputation points Microsoft External Staff Moderator

2025-11-11T06:31:42.2+00:00

Hi SPorter,

I hope you had a chance to review the information shared earlier, and I hope this information has been helpful! If you still have questions, please let us know what is needed in the comments so the question can be answered.

Answer 1

Hi SPorter,

Welcome to the Microsoft Q&A and thank you for posting your questions here.

Yes, Azure Data Factory (ADF) can be used to handle this kind of data update/insert scenario by implementing an Upsert operation. You can use a stored procedure to handle the logic directly in the database. A stored procedure can perform an upsert operation using SQL’s MERGE statement. Edit the below Merge statement as per your need and put it in a SP.

MERGE INTO TargetTable AS target

USING SourceTable AS source

ON target.PrimaryKey = source.PrimaryKey

WHEN MATCHED THEN

UPDATE SET

target.Column1 = source.Column1,

target.Column2 = source.Column2

-- Include all the columns you want to update

WHEN NOT MATCHED THEN

INSERT (PrimaryKey, Column1, Column2)

VALUES (source.PrimaryKey, source.Column1, source.Column2);

You can call this stored procedure from an Azure Data Factory pipeline using a Stored Procedure Activity.

Here's how you can call a Stored Procedure in ADF:

Create the Stored Procedure: Implement the MERGE logic in your SQL database as described above.
Call the Stored Procedure: In ADF, create a pipeline and add a Stored Procedure activity. Connect this activity to your SQL Database Linked Service. Set the procedure name and provide necessary parameters (if any).
Debug/Trigger the pipeline.

I hope this information is helpful! If you still have questions or you come across issues, please let us know what is needed in the comments so this question can be answered.

Answer 2

SPorter 0

I have over 150 of the attached files. how do i run this quickly?

SPorter 0 Reputation points

2025-11-07T18:14:58.2666667+00:00

Currently opening them individually each month
Manoj Kumar Boyini 1,250 Reputation points Microsoft External Staff Moderator

2025-11-09T09:01:58.07+00:00
Hi SPorter,

Thank you for sharing the details and screenshot. We understand that opening and running over 150 SQL files manually each month takes a lot of time. The best way to automate this is by using ADF it can process all files automatically and handle both update and insert (upsert) operations.

In ADF you can build a pipeline that reads all files from a folder using the Get Metadata activity and loops through them with a ForEach activity. Inside that loop, use either:

a Stored Procedure activity to call your SQL MERGE procedure (for precise control), or

a Copy Data or Mapping Data Flow activity with the Upsert option (for low-code automation).

For better performance, it’s recommended to bulk load each file into a staging table and then run one MERGE to update and insert rows. This approach is much faster and prevents duplicates.

Once set up ADF will automatically go through all 150+ files, update or insert the data, and remove the need for any manual steps.

Share via

Data was not updating rows that contained existing primary key

2 answers

Your answer