How to upsert data using copy activity and Stored Procedure in Azure Data Factory

Overview

This documentation provides step-by-step instructions on how to create a data pipeline in the Azure portal to copy data from a CSV file to a staging table and then sink it to the main table. The data pipeline utilizes Azure services such as Azure Data Factory and Azure SQL Database to achieve the data copying and sinking processes.

Prerequisites

Before creating the data pipeline, ensure that you have the following prerequisites in place:

1. Azure subscription: You should have an active Azure subscription to create and configure the necessary resources.

2. Azure Data Factory: Make sure you have an Azure Data Factory (ADF) instance provisioned in your Azure subscription.

3. Azure SQL Database: Set up an Azure SQL Database instance where the staging table and main table will reside.

4. Basic Understanding of Data Factory (Pipelines & Copy Activity), Azure SQL Database: If not, I would recommend you first walk through it; you can find tons of articles on the internet.

Steps:

1. I am expecting that you already have a table available in your Azure SQL database that you want to upsert (update & insert).

Initial Table

2. Create a Dataset for the CSV file

A Dataset represents the CSV file that you want to copy data from.

3. Create a staging table in the Azure SQL Database.

SQL query to create staging table in Azure SQL db

4. Create Copy Activity

The Copy Activity defines the movement of data from the CSV file to the staging table.

4. Create Stored Procedure for Sink Data

To move the data from the staging table to the main table, you can use a stored procedure.

5. Create a Stored Procedure Activity.

6. Publish the pipeline and hit the trigger to reflect the changes.

Table after Upsert

PS: I have attempted to outline the main steps of the process, but I may have skipped some basic details. If you have any doubts about that, I’m confident that you will find a wealth of information on the internet.