Upsert in SQL Server

Do you know how to do an UPSERT in SQL Server ?

How do we use MERGE in SQL ?

Often there are situations when we need to sync up the tables in our database from the JOIN operation with source table so that the data is either updated or inserted or deleted based on a certain criteria.

UPSERT is a term used for an INSERT or UPDATE an infact UPSERT is a command available in ORACLE but in we can implement the UPSERT functionality in SQL Server using in the below mentioned ways:-

1. Using Conditions - Checking if the records which is inserted already exists in the target table or not based on the criteria.

If the record with a matching criteria already exists then we update the existing records.

If the records with a matching criteria does not exist then we insert a records.

Now these operation would require writing multiple statements to achieve the result. Also if there are a larger number of tables where we need to accommodate these requirement then this approach would be performance intensive.

2. Using a MERGE command – We can write a MERGE Statement specifying the matching criteria and perform the INSERT / UPDATE / DELETE operations

The result of a MERGE operation can be either INSERT, UPDATE or DELETE based on the criteria defined in the MERGE Statement.

In this post we are going to cover the second approach of using a MERGE command to perform an UPSERT operation in SQL Server.

The MERGE command works best when we have a complex matching criteria to be used for INSERT/UPDATE/DELETE between the source and the target table.

We should continue using the first approach when we just need to UPSERT based on the existence of the record in another table when there are no matching criteria needed between the source and the target table.

A typical MERGE statement would look something like this –

MERGE target_table USING source_table
ON matching_criteria
WHEN MATCHED
    THEN update target
WHEN NOT MATCHED
    THEN insert target
WHEN NOT MATCHED BY SOURCE
    THEN DELETE;

Now let us see an example – Here we would be creating two table SOURCE_STUDENT and TARGET_STUDENT using the scripts mentioned below:-

--CREATE SOURCE TABLE

CREATE TABLE SOURCE_STUDENT (ID INT,[NAME] VARCHAR(50))

--INSERT RECORDS IN SOURCE TABLE
INSERT INTO SOURCE_STUDENT VALUES (1,'Eleanor Hill')
INSERT INTO SOURCE_STUDENT VALUES (2,'Moore Morrow')
INSERT INTO SOURCE_STUDENT VALUES (3,'Jeannette Warren')

--CREATE TARGET TABLE
CREATE TABLE TARGET_STUDENT (ID INT,[NAME] VARCHAR(50))

--INSERT RECORDS IN TARGET TABLE
INSERT INTO TARGET_STUDENT VALUES (4,'Esperanza Kaufman')
INSERT INTO TARGET_STUDENT VALUES (1,'Eleanor Bill')
INSERT INTO TARGET_STUDENT VALUES (5,'Amie George')

SOURCE_STUDENT

TARGET_STUDENT

MERGE TARGET_STUDENT AS T 
USING SOURCE_STUDENT AS S
ON T.ID = S.ID
WHEN MATCHED THEN UPDATE  SET T.NAME = S.NAME
WHEN NOT MATCHED BY TARGET THEN INSERT (ID,[NAME]) VALUES (ID,[NAME])
WHEN NOT MATCHED BY SOURCE THEN DELETE

OUTPUT $ACTION,INSERTED.ID,INSERTED.[NAME],DELETED.ID,DELETED.[NAME];

Final Target_Student table

In our example we have taken a very basic table structure just to demostrate the concept and the usage of MERGE for performing UPSERT in SQL but it can be extended to more complex scenarios based on our usecase.

If you want to take a deep dive into the MERGE command you can follow the below mentioned link - https://docs.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql?view=sql-server-ver15