The MERGE Statement in SQL Server

Introduction

This article gives an explanation about the MERGE statement in SQL server. Basically, the MERGE statement in SQL server joins the result set of target table to the result set of source table by using a common column to both tables and you can insert, update and delete at the same time in a single atomic statement, on the target table.

While you working with any data-driven applications, many developers face issues when they want to perform multiple Insert, Update and Delete statements. To overcome this kind of problem in SQL server there is an option to use the MERGE statement that allows you to do this all the statements at once.

Requirement

1) Introduction SQL Server MERGE Statement

2) Syntext of MERGE Statement in SQL server

2) Explain MERGE Statement in SQL server with Example.

The use of MERGE Statement in SQL server?

In SQL server MERGE Statement is used to perform multiple Insert, Update and Delete statements at the same time in a single atomic statement.

Consider the following scenario, suppose, you have two SQL table/result set called as the source table and target tables, and you need to insert/update/delete the records into the target table based on the values matched from the source table. There are three possible cases as I showed below.

INSERT

Suppose your source table has some records that do not exist into your target table. So, In this scenario, you need to insert that records that are available in the source table into your target table.

DELETE

Now, suppose your target table has some records that do not exist into your source table. So, In this scenario, you need to delete that records from your target table.

UPDATE

Now, suppose you have some records in your source table with the same keys as the records available in your target table. However, some of these records/rows have some different values in the non-key columns. now, In this scenario, you have to update the records in your target table with the values coming from your source table.

Syntext of MERGE Statement

MERGE Your_target_table USING Your_source_table
ON Your_merge_condition
WHEN MATCHED
    THEN Your_update_statement
WHEN NOT MATCHED
    THEN Your_insert_statement
WHEN NOT MATCHED BY SOURCE
    THEN DELETE;

So, let's start with an example so you can get more idea about the MERGE statement in SQL server.

Example

We will create two tables with the same column first is tblSales_Master_Target as Target table and tblSales_Master_Source as the source table. Now we will insert some of the dummy records for demonstration purpose.

Target Table

CREATE TABLE tblSales_Master_Target (
    Sales_id INT PRIMARY KEY,
    Customer_name VARCHAR(255) NOT NULL,
    Product_name VARCHAR(255) NOT NULL,
    Amount FLOAT
);

Insert Records into Target Table

INSERT INTO tblSales_Master_Target(Sales_id, Customer_name,Product_name, Amount)
VALUES(1,'Nikunj Satasiya','Lenovo Laptop',45000),
(2,'Karishna Patel','LG TV',20000),
(3,'Hiren Dobariya','Mobile',20000),
(4,'Vivek Ghadiya','MI TV',14999);

SELECT * FROM tblSales_Master_Target

Source Table

CREATE TABLE tblSales_Master_Source (
    Sales_id INT PRIMARY KEY,
    Customer_name VARCHAR(255) NOT NULL,
    Product_name VARCHAR(255) NOT NULL,
    Amount FLOAT
);

Insert Records into Source Table

INSERT INTO tblSales_Master_Source(Sales_id, Customer_name,Product_name, Amount)
VALUES(1,'Nikunj Satasiya','Lenovo Laptop',45000),
    (3,'Hiren Dobariya','Mobile',20000),
    (4,'Vivek Ghadiya','MI TV',28500),
    (5,'Shreya Patel','Dell Laptop',33000),
    (6,'Mohit Patel','AC',25000);

SELECT * FROM tblSales_Master_Source

Now, we will marge target and source table and perform multiple Insert, Update and Delete statements using a common column to both tables.

MARGE Statment in SQL Server

MERGE tblSales_Master_Target tblTarget 
    USING tblSales_Master_Source tblSource
ON (tblSource.Sales_id = tblTarget.Sales_id)
WHEN MATCHED
    THEN UPDATE SET 
 tblTarget.Customer_name = tblSource.Customer_name,
        tblTarget.Product_name = tblSource.Product_name,
        tblTarget.Amount = tblSource.Amount
WHEN NOT MATCHED BY TARGET 
    THEN INSERT (Sales_id, Customer_name,Product_name, Amount)
         VALUES (tblSource.Sales_id, tblSource.Customer_name, tblSource.Product_name, tblSource.Amount)
WHEN NOT MATCHED BY SOURCE 
    THEN DELETE;

Explanation

As you can see in the above example there are two different tables target and source table with the same column. The target table contains 4 records with product and customers detail and the source table has 5 records. Some of the records are same in both table but some of the records are missing in the target table as well as some of the records that are available in the source table but not in the target table.

Insert, Update and Delete Record from Table

Look, Sales_id(2) is available in Target table but not in the source table so, Sales_id(2) should be removed from the target table and same as Sales_id(5) and Sales_id(6) is available in the source table so that records should be inserted in the target table. All other matched records should be updated in the target table based on the value comes from the source table for the particular sales record.

To archive, this kind of requirement SQL server provided a MARGE statement where you can do all the operations as I discussed above for INSERT records in the Target Table based on the source table for missing entries of sales record int the target table, as well as UPDATE matched records in the target table based on the value, comes from source table, and finally DELETE those entries from the target table which is not available in the source table but available in the target table.

Here, in the above example, we have used MARGE statement to do all the operation INSERT, UPDATE and DELETE in a single statement at once and marge both tables using the statement like MERGE tblSales_Master_Target and give alias of the table tblTarget with USING clause like USING tblSales_Master_Source and give alias of the table tblSource on the same key of both table like primary key of both table which is Sales_id. Now checked WHEN MATCHED means when matching any records in both table then we updating Target table based on value coming from the Source table so all the value will be updated in a target table based on source table values. Now, we checked for not matched value with target table using the WHEN NOT MATCHED BY TARGET then we inserting that entries into the target table based on value coming from the source table and finally check for the records which are not matched in the source table using WHEN NOT MATCHED BY SOURCE and delete these records from the

target table.

OUTPUT

Target Table Before/After

Insert, Update and Delete Record from Table using MARGE Statement

Summary

In this article, we learned what is MARGE statement? how to use MARGE statement in SQL server, The use of MARGE statement in SQL server, How we can do INSERT, UPDATE and DELETE operations in a single statement at the same time with an example.

Still, If you have any questions regarding MARGE statement you can post your questions as comments in the comment section, I am available here to resolve your queries and your questions.