SQL Server Merge Statement

Introduction

Here we are going to discuss SQL - Merge statement introduced with SQL server 2008.

We are going to cover,

  1. What is MERGE Statement
  2. Syntax of MERGE Statement
  3. UPDATE Record using MERGE Statement.
  4. INSERT Record using MERGE Statement.
  5. DELETE Record using MERGE Statement.
  6. TOP Clause with MERGE Statement.
  7. OUTPUT clause with MERGE Statement.

What is MERGE Statement?

In the real world, many times we need to sync or merge two tables. For example, we have two tables

  1. Target
  2. Source

We want to sync both tables.

I mean,

  1. if the record does not exist in the target table, then we want to insert it from Source Table,
  2. if a record exists in the Target Table then update it from the Source table.
  3. or record exists in the source table and not in the target table then delete from Target table.  

Any guess as to how will you achieve it before SQL 2008?

The answer would be writing three different SQL statements: Insert, Update and Delete, based on the condition. This is where the MERGE statement comes into the picture. It will help you to achieve this in a single SQL statement instead of three different statements.

“The Merge statement is a very popular clause in SQL which is mainly used to handled three different statements (insert, Update and Delete) in a single statement called MERGE.”

With MERGE Statement we require 2 tables,

  1. Source Table – This table containing the changes that need to be applied to the Target Table.
  2. Target Table – The table which required changes.

Let’s see the below image to get understanding,

Syntax of MARGE Statement

MERGE TOP (value) <target_table> 
USING <table_source>   
ON <merge_search_condition>  
    [ WHEN MATCHED [ AND <clause_search_condition> ]  
        THEN <merge_matched> ] 
    [ WHEN NOT MATCHED [ BY TARGET ] [ AND <clause_search_condition> ]  
        THEN <merge_not_matched> ]  
    [ WHEN NOT MATCHED BY SOURCE [ AND <clause_search_condition> ]  
        THEN <merge_matched> ] 
    [ <output_clause> ]  
    [ OPTION ( <query_hint> ) ]      
;

Please Note that MERGE Statement must terminate by “;”.

To get a better understanding, we are going to create two tables like below,

The below script helps you to create tables with sample data.

CREATE TABLE [dbo].[MemberSource](
	[Id] [int] NOT NULL,
	[Name] [varchar](30) NOT NULL
)
GO

CREATE TABLE [dbo].[MemberTarget](
	[Id] [int] NOT NULL,
	[Name] [varchar](30) NOT NULL
)
GO

INSERT INTO MemberSource
values(1,'Kirtesh'),
(2,'Nitya')
GO

INSERT INTO MemberTarget
values(1,'Rajesh'),
(3,'Mahesh')
GO

Now we will use the MERGE Statement to Insert, Update and Delete records in the “MemberTarget” table from the “MemberSource” table.

UPDATE MATCH RECORDS WITH MERGE

Both of the tables have ID=1. If data exists in the target table then update the record from the source table. So in this case we will update the record from MemberSource to the MemberTarget table.

WHEN MATCH Clause – This Clause is used to UPDATE and DELETE records based on match conditions.

Only Two WHEN MATCHED clauses can be in the MERGE Statement. If we have 2 WHEN MATCHED clauses in the MERGE Statement,

  1. One should be used for UPDATE
  2. And another should used for DELETE.

Both WHEN MATCHED Clause cannot be used with UPDATE OR DELETE statement in MERGE.

In our case “MemberSource” is the source table, “MemberTarget” is the Target table, and ID is used for joining conditions.

Let's see the below script,

MERGE MemberTarget T
USING MemberSource S ON T.ID=S.ID
WHEN MATCHED THEN
UPDATE SET T.Name= S.Name;

 

Let's execute the below script to see the output of the above script.

SELECT * FROM [MemberTarget]

Result

Record is updated successfully for ID =1.

We can also add additional search conditions with WHEN MATCHED conditions.

MERGE MemberTarget T
USING MemberSource S ON T.ID=S.ID
WHEN MATCHED AND T.ID=1 THEN
UPDATE SET T.Name= S.Name;

A question comes to mind: What will happen if more than one row matches with the condition of MERGE Statement?

UPDATE Or DELETE Will be failed and throw error message “The Merge statement attempts to UPDATE or DELETE the same row more than once.”

INSERT USING MERGE STATEMENT

WHEN NOT MATCHED BY TARGET clause is used to insert a row in the Target table. Please note that WHEN NOT MATCHED BY TARGET clause can use only once in the MERGE Statement.

In our case, ID =1 and Name=Nitya row is not present in the Source table. We will use the MERGE statement to insert this row in the Target table.

Let’s see the below script,

MERGE MemberTarget T
USING MemberSource S ON T.ID=S.ID
WHEN NOT MATCHED BY TARGET THEN
INSERT (ID,Name)
values (S.ID,S.Name);

The above script adds a new row in the Target table.

SELECT * FROM [MemberTarget]

Result

We can use additional search conditions with WHEN NOT MATCHED BY TARGET in the MERGE Statement.

DELETE USING MERGE STATEMENT

WHEN NOT MATCHED BY SOURCE can be used to delete records from the Target table based on the join condition. This can be used only once in the MERGE Statement.

In case of 2 WHEN NOT MATCHED BY SOURCE Clause,

  1. One can use it for UPDATE.
  2. And Another should use for DELETE.

Let's see the below script,

MERGE MemberTarget T
USING MemberSource S ON T.ID=S.ID
WHEN NOT MATCHED BY SOURCE THEN
DELETE;

Now use the below script for the result,

SELECT * FROM [MemberTarget]

Result

Below diagram explains to you all the changes we did today,

Consolidated MERGER Script,

MERGE MemberTarget T
USING MemberSource 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 (S.ID,S.Name)

WHEN NOT MATCHED BY SOURCE THEN
DELETE;

TOP Clause in MERGE Statement

We can use Top clause with MERGE Statement to limit the number of rows that will be affected.

Suppose we have tables like below,

Let's write TOP Clause to limit the number of rows affected is 1 with MERGE Statement,

MERGE TOP (1) MemberTarget T
USING MemberSource 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 (S.ID,S.Name)

WHEN NOT MATCHED BY SOURCE THEN
DELETE;

Result

SELECT * FROM MemberTarget

See below diagram to understand TOP clause with MERGE Statement,

It has updated only one record and not inserted and deleted records in the target table as we have applied the Top clause in the MERGE statement.

OUTPUT Clause with MERGE

Output clause helps us to understand that row is inserted, updated, or deleted in the target table. $action is used to know row is inserted, Updated, or Deleted. Let's see the below script,

MERGE MemberTarget T
USING MemberSource 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 (S.ID,S.Name)

WHEN NOT MATCHED BY SOURCE THEN
DELETE
OUTPUT DELETED.*, $action AS [Action], INSERTED.* ;

Result

That’s all for this article. I hope you enjoyed it and find useful.