SQL Server Merge Statement

Databases are all about CRUD operations. We create, read, update, and delete data into various database tables. However, most CRUD operations in a database require separate logic for tasks, such as inserting, updating, and deleting.

This can get redundant quickly. SQL Server provides an efficient way of performing CRUD operations using the MERGE statement. Microsoft introduced the merge statement in SQL Server 2008 and above.

This tutorial will understand how to use the SQL Server merge statement to perform multiple operations in a single query.

The Basics

Let us take an example of where you have two tables. The target and source tables. If you need to update the values in the target table based on the values from the source tables, you can take three paths:

1. The first is where the source contains missing rows in the target table. You need an insert statement to the target table in such a case.
2. The second is where the target table contains missing records from the source table. Here, we need a delete statement to remove the rows from the target.
3. The last scenario is where a record in source and target holds different values. We need an update statement to the target table in such a case.

To perform the above operations individually, we need to create three separate logic for insert, delete, and update operations. However, we can combine them using the Merge statement.

We can express the syntax of the merge statement as shown:

MERGE target_table USING source_table
ON condition
WHEN matched
        THEN update_operation
WHEN NOT matched -- by target table
        THEN insert_operation
WHEN NOT matched BY SOURCE
        THEN DELETE;

We identify the target and source table and specify them in the merge clause. We then specify a condition. The specified condition controls how the rows from the source table are matched to the target tables. Think of it as a join condition.

The next block holds the actions to be performed based on the result of the specified condition.

If the condition results in a match, we update the records in the target table from the source table.

However, if the records are unmatched (from the target table), we insert the missing records in the target table.

Finally, if the records are unmatched (by the target table), we delete the unmatched records from the target table.

SQL Server – Merge Example

Let us take a simple example. Assume we have two tables containing product information as products_target and product_source.

The example code snippet shows the SQL queries to create and update the specified tables.

USE salesdb;
CREATE TABLE Products_target (
        product_id INT NOT NULL PRIMARY KEY IDENTITY(1,1),
        product_name VARCHAR(255) NOT NULL,
        price DECIMAL(10,2)
);
INSERT INTO Products_target(product_name, price)
VALUES ('Office Desk', 531),
                ('Office Chair', 379.99),
                ('Water Bottle', 69.99);
CREATE TABLE Products_source (
        product_id INT NOT NULL PRIMARY KEY IDENTITY(1,1),
        product_name VARCHAR(255) NOT NULL,
        price DECIMAL(10,2)
);
INSERT INTO Products_source(product_name, price)
VALUES ('Office Desk', 531.00),
                ('Desk Lamp', 50.00),
                ('Office Chair', 699.99),
                ('Water Bottle', 89.95);

Now we have two tables performing as a target and source. The records stored in the tables are as shown:

To synchronize the data between the target and source table, we can execute a merge query as shown in the example below:

MERGE Products_target AS t
USING Products_source AS s
ON (s.product_id = t.product_id)
WHEN matched
        THEN UPDATE SET
                t.product_name = s.product_name,
                t.price = s.price
WHEN NOT matched BY target
        THEN INSERT(product_name, price)
                VALUES (s.product_name, s.price)
WHEN NOT matched BY SOURCE
        THEN DELETE;

Once we execute the above query, the SQL server will perform the specified operations based on the resulting condition.

We can query the tables after the merge operation as:

SELECT * FROM Products_source;
SELECT * FROM Products_target;

The resulting values are as shown in the example below:

As you will notice, the source and target table records are synchronized with the values updated, inserted, and deleted.

Conclusion

This guide shows you how to work with the SQL Server merge statement. It allows you to perform insert, update, and delete operations on tables based on the resulting conditions.

Thank you for reading!

from https://ift.tt/33IJKOD