Introduction to the SQL Server MERGE statement

The MERGE statement in SQL provides a streamlined way to insert, update, and delete data as part of a single batch statement. With the potential benefits of cleaner code and performance improvements for certain scenarios, the MERGE statement is a great tool to have up your sleeve.

In this article, I will demonstrate how the SQL Server implementation of the MERGE statement works, covering the inserting, updating, and deleting of records from a sample data-set.

Environment Setup

If you want to follow along, I recommend that you install the latest version of SQL Server or LocalDB (usually you will already have access to LocalDB if you have Visual Studio installed) and the latest version of SQL Server Management Studio (SSMS) or Azure Data Studio for interacting with the SQL Server database.

Note that support for the MERGE statement was added in SQL Server 2008.

Database Schema

In order to explore how the MERGE statement works, we’ll need a sample database schema.

After connecting to your SQL Server instance via SSMS or Azure Data Studio, you can use the following SQL script to create a test database with two tables and some sample data.

CREATE DATABASE MergeDemo;
GO
 
USE MergeDemo;
 
CREATE TABLE dbo.SupplierProducts
(
    Id INT IDENTITY(1, 1) NOT NULL,
    Sku NVARCHAR(25) NOT NULL,
    Title NVARCHAR(255) NOT NULL,
    Price DECIMAL(18, 2) NOT NULL,
    CONSTRAINT PK_SupplierProducts_Id PRIMARY KEY CLUSTERED 
    (
        Id ASC
    ),
    CONSTRAINT UQ_Products_Sku UNIQUE(Sku)
);
 
INSERT INTO dbo.SupplierProducts(Sku, Title, Price) VALUES('5063263284247', 'Whisk Peanut Butter', 3.10);
INSERT INTO dbo.SupplierProducts(Sku, Title, Price) VALUES('5034543538332', 'Summit Trail Mix', 2.30);
INSERT INTO dbo.SupplierProducts(Sku, Title, Price) VALUES('80322486', 'Strawberry Jam', 2.15);
INSERT INTO dbo.SupplierProducts(Sku, Title, Price) VALUES('5046375632832', 'Mineral Water 500ml', 1.40);
 
CREATE TABLE dbo.Products
(
    Id INT IDENTITY(1, 1) NOT NULL,
    Code NVARCHAR(25) NOT NULL,
    [Name] NVARCHAR(255) NOT NULL,
    CostPrice DECIMAL(18, 2) NOT NULL,
    RetailPrice DECIMAL(18, 2) NULL,
    CONSTRAINT PK_Products_Id PRIMARY KEY CLUSTERED 
    (
        Id ASC
    ),
    CONSTRAINT UQ_Products_Number UNIQUE(Code)
);
 
INSERT INTO dbo.Products(Code, [Name], CostPrice) VALUES('5063263284247', 'Peanut Butter', 3.10);
INSERT INTO dbo.Products(Code, [Name], CostPrice) VALUES('5034543538332', 'Trail Mix', 2.30);

The script creates a database named ‘MergeDemo’ and switches context to the newly created database via the USE keyword.

A ‘SupplierProducts’ table is then created and some sample data is inserted. This will act as the ‘source’ table for demonstration purposes.

Lastly, a ‘Products’ table is created and sample data is inserted. Notice that the ‘Products’ table has a similar schema but has different column names and an extra ‘RetailPrice’ column.

The screenshot below shows what the contents of the ‘SupplierProducts’ and ‘Products’ tables look like after inserting the sample data.

Merging

Now that the environment has been set up, let’s dive in and use the MERGE statement with the data.

Inserting

In the simplest scenario, only one of the possible MERGE sub-statements are specified.

For our demo, we’ll start with handling the scenario where rows in the source table do not have a match in the target table.

See the SQL example below.

DECLARE @MarkupPercentage INT = 40;
 
MERGE dbo.Products AS target
USING dbo.SupplierProducts AS source
ON source.Sku = target.Code
 
WHEN NOT MATCHED BY target THEN
    INSERT (
        Code,
        [Name],
        CostPrice,
        RetailPrice
    )
    VALUES (
        source.Sku,
        source.Title,
        source.Price,
        source.Price * (1 + @MarkupPercentage / 100.0)
    );

In the above example, an @MarkupPercentage variable is initialised which is used later to calculate a marked-up value for the ‘RetailPrice’ column in the ‘Products’ table.

We begin the MERGE statement by specifying the ‘target’ i.e. the table that we want to insert data into for this first example. After this, we specify the ‘source’ i.e. the table that we want to insert data from. Then we specify how we are connecting the source and target tables; in this case, we are joining on the SKU/Code columns.

Now for our first sub-statement, the WHEN NOT MATCHED BY target block will be executed when one or more rows in the source table do not have a match in the target table. Within the block, we choose the target columns to insert into and the values from the source table that we want to insert.

When the SQL is executed, the code will detect that there are two records in the ‘SupplierProducts’ (source) table that don’t have a matching SKU/Code in the ‘Products’ (target) table. Therefore, two new rows will be added to the ‘Products’ table.

Updating

Let’s take a look at handling updates next.

We’ll build on the previous code, adding a new sub-statement to the end of the SQL example, as shown below.

DECLARE @MarkupPercentage INT = 40;
 
MERGE dbo.Products AS target
USING dbo.SupplierProducts AS source
ON source.Sku = target.Code
 
WHEN NOT MATCHED BY target THEN
    INSERT (
        Code,
        [Name],
        CostPrice,
        RetailPrice
    )
    VALUES (
        source.Sku,
        source.Title,
        source.Price,
        source.Price * (1 + @MarkupPercentage / 100.0)
    )
 
WHEN MATCHED THEN UPDATE SET 
    target.Code = source.Sku,
    target.[Name] = source.Title,
    target.CostPrice = source.Price,
    target.RetailPrice = source.Price * (1 + @MarkupPercentage / 100.0);

In the above SQL, a WHEN MATCHED condition has been added, along with a block of code to handle the updates. The WHEN MATCHED block will be executed when the ‘Sku’ column in the ‘SupplierProducts’ (source) table matches the ‘Code’ column in the ‘Products’ (target) table, as specified near the top of the MERGE statement.

Within the WHEN MATCHED block, the ‘Products’ (target) table columns are updated with the appropriate column values from the ‘SupplierProducts’ (source) table.

When the SQL is executed, the code will detect that there is a match on all 4 rows in the ‘Products’ (target) table and will update the column values accordingly. Before executing the query, 2 of the products had a different Title/Name in the ‘SupplierProducts’ (source) table and these differences will be reflected in the ‘Products’ (target) table after the updates have been applied.

Additionally, the ‘RetailPrice’ column has been updated according to the price mark-up calculation.

Note that you can add custom conditions to the WHEN MATCHED sub-statement to provide more control over when updates are applied e.g. you could write WHEN MATCHED AND target.[Name] <> source.Title to only update the products when the Name/Title is different between the tables.

Deleting

Lastly, let’s see how deletes are handled.

Before inspecting the revised code, run the following SQL to delete a specific row from the ‘SupplierProducts’ (source) table.

DELETE FROM dbo.SupplierProducts WHERE Sku = '5034543538332';

Handling deletes is the easiest part of the MERGE statement to implement.

See the updated SQL example below for reference.

DECLARE @MarkupPercentage INT = 40;
 
MERGE dbo.Products AS target
USING dbo.SupplierProducts AS source
ON source.Sku = target.Code
 
WHEN NOT MATCHED BY target THEN
    INSERT (
        Code,
        [Name],
        CostPrice,
        RetailPrice
    )
    VALUES (
        source.Sku,
        source.Title,
        source.Price,
        source.Price * (1 + @MarkupPercentage / 100.0)
    )
 
WHEN MATCHED THEN UPDATE SET 
    target.Code = source.Sku,
    target.[Name] = source.Title,
    target.CostPrice = source.Price,
    target.RetailPrice = source.Price * (1 + @MarkupPercentage / 100.0)

WHEN NOT MATCHED BY source THEN      
    DELETE;

The WHEN NOT MATCHED BY source condition essentially says that if the ‘Sku’ column in the ‘SupplierProducts’ (source) table doesn’t have a match on the ‘Code’ column in the ‘Products’ (target) table, then the row should be deleted.

When the SQL is executed, the code will detect that one of the records in the ‘Products’ (target) table does not have a matching row in the ‘SupplierProducts’ (source) table and that row will be deleted.

Summary

In this article, I demonstrated how the SQL Server MERGE statement works.

I started with the environment setup, including the required tools and the creation of a sample database schema and data to use for demonstrating the workings of the MERGE statement.

I then walked through how to write a MERGE statement that can handle inserts, updates, and delete scenarios so that you can synchronise data seamlessly from a source table to a target table.