MERGE Statement (Transact-SQL)

Introduction

The MERGE statement in Transact-SQL allows you to combine insert, update, and delete operations into a single statement. It's useful for synchronizing data between two tables, either by adding new records, updating existing ones, or deleting records that don't exist in the target table. The MERGE statement is particularly effective when dealing with data loading, data warehousing, and other situations where you need to manage data consistency across multiple sources.

Syntax


MERGE INTO target_table AS t
USING source_table AS s
ON (t.primary_key_column = s.primary_key_column)
WHEN MATCHED THEN
    UPDATE SET
        t.column1 = s.column1,
        t.column2 = s.column2
WHEN NOT MATCHED THEN
    INSERT (column1, column2)
    VALUES (s.column1, s.column2)
;

Parameters

target_table: The name of the table to be modified.
source_table: The name of the table containing the source data.
ON: Specifies the join condition between the target and source tables. This is typically based on a common key column.
WHEN MATCHED: Defines the actions to be taken when a matching row is found in both tables. This usually involves updating the target table with the values from the source table.
WHEN NOT MATCHED: Defines the actions to be taken when no matching row is found in the target table. This typically involves inserting a new row into the target table.

Examples

Example 1: Adding New Products


MERGE INTO Products AS p
USING Products_New AS pn
ON (p.ProductID = pn.ProductID)
WHEN MATCHED THEN
    UPDATE SET
        p.ProductName = pn.ProductName,
        p.Price = pn.Price
WHEN NOT MATCHED THEN
    INSERT (ProductID, ProductName, Price)
    VALUES (pn.ProductID, pn.ProductName, pn.Price);

Example 2: Updating Customer Information


MERGE INTO Customers AS c
USING Customers_Update AS cu
ON (c.CustomerID = cu.CustomerID)
WHEN MATCHED THEN
    UPDATE SET
        c.Address = cu.Address,
        c.City = cu.City
WHEN NOT MATCHED THEN
    -- Optionally, you can handle cases where a customer exists in the source but not the target.
    -- For example, you could log the missing customer or update the target customer's information.
    -- RAISEERROR('Customer not found', 16, 1);
    ;