How To Use SQL MERGE Statement

Introduction 

INSERT, UPDATE, and DELETE are the three SQL statements that make up the MERGE command in SQL. Simply said, the SQL MERGE statement offers a straightforward mechanism to execute all three of these actions simultaneously, which can be very useful when managing big operating databases. Unlike INSERT, UPDATE, and DELETE statements, MERGE statements demand a source table to carry out these actions on the needed table, also known as the target table. Another way to describe it is that this statement links the required table with the target table before performing the required actions. This command offers more flexibility for editing our intricate SQL code, making it easier to read. By comparing a key field with that of the other table, it basically updates an existing table.

MERGE Statement in SQL Server

 First, we create two tables, one is the product and the other is product_v2.

CREATE TABLE Product (
    Product_id INT PRIMARY KEY,
    Product_Name VARCHAR(255) NOT NULL,
    amount DECIMAL(10 , 2 )
);

INSERT INTO Product(Product_id, Product_Name, amount)
VALUES(1,'almonds',1500),
    (2, 'Apricot',500),
    (3, 'Volnut',300),
    (4, 'Dates',1000);

CREATE TABLE Product_v2 (
    Product_id INT PRIMARY KEY,
    Product_Name VARCHAR(255) NOT NULL,
    amount DECIMAL(10 , 2 )
);

INSERT INTO Product_v2 (Product_id, Product_Name, amount)
VALUES(1,'almonds',1500),
    (3, 'Volnut',1300),
    (4, 'Dates',2000),
    (5, 'Cashew',1000),
    (6, 'Coconut',1000);

Output

Product table act as Target Table and the product_v2 table act as Source Table,

• Some rows in the source table do not exist in the destination table. In this situation, you must add rows from the source table to the target table.
• Some rows in the target table are absent from the source table. You must in this instance remove rows from the target table.
• Specific rows in the target table and some rows in the source table share the same keys. The non-key columns in these rows, however, have different values. In this scenario, it is necessary to update the rows in the target table with data from the source table.

To update the data in the target table with the matching rows from the source table, you must construct three different statements if you use the INSERT, UPDATE, and DELETE statements separately.

Third, the three states that the merge condition outputs are MATCHED, NOT MATCHED, and NOT MATCHED BY SOURCE.

	MERGE Product p 
    USING Product_v2 pv
ON (pv.Product_id = p.Product_id)
WHEN MATCHED
    THEN UPDATE SET 
        p.Product_Name = pv.Product_Name,
        p.amount = pv.amount
WHEN NOT MATCHED BY TARGET 
    THEN INSERT (Product_id, Product_Name, amount)
         VALUES (pv.Product_id, pv.Product_Name, pv.amount)
WHEN NOT MATCHED BY SOURCE 
    THEN DELETE;

This is the output after running the above code. 3 updates, 1 deletion, and 2 inserts were made.

Output

Conclusion 

Because all data is read and processed just once, the statement enhances performance. Earlier versions required three separate statements to perform three different actions, such as INSERT, UPDATE, or DELETE, when data from both the source and destination tables were evaluated.

If you have any queries/suggestions on the article, please leave your questions and thoughts in the comment section below. Follow C# Corner to learn more new and amazing things about SQL Server or to explore more technologies.

Thanks for reading and I hope you like it.