The MERGE statement is a versatile feature introduced in SQL Server 2008 that allows the combination of INSERT, UPDATE, and DELETE operations into a single statement. When paired with the OUTPUT clause, it becomes even more powerful by enabling us to capture the results of these actions, providing us visibility into what changes occurred during the merge.

In this article, we’ll walk through an example that uses the OUTPUT clause during a MERGE operation in SQL Server.

About the MERGE Statement

The MERGE statement in SQL Server allows us to perform multiple operations (INSERT, UPDATE, DELETE) on a target table in a single, atomic operation. The statement compares the target table with a source table based on a specified condition and then performs the appropriate operation depending on whether a match is found.

This makes it useful in scenarios such as:

• Synchronizing two tables.
• Upserting (inserting new records or updating existing ones).
• Tracking changes between datasets.

About the OUTPUT Clause

The OUTPUT clause captures and returns the rows affected by an INSERT, UPDATE, DELETE, or MERGE statement. It allows us to access the changes made during the execution, providing insight into how the data was modified.

The OUTPUT clause can return:

• Inserted values: Data that was added to the target table.
• Deleted values: Data that was removed from the target table.
• Updated values: Data that was modified in the target table (i.e. the “before” and “after” values).

In the context of a MERGE operation, the OUTPUT clause can return a combination of inserted, deleted, and updated rows, making it a useful tool for auditing and logging.

Why Use the OUTPUT Clause with MERGE?

Using the OUTPUT clause with the MERGE statement has several benefits:

• Auditing and logging: We can track exactly what changes were made, such as which rows were updated, inserted, or deleted.
• Validation: By returning the affected rows, we can validate that the correct operations were performed.
• Performance benefits: Instead of running multiple queries to determine the changes made, we can capture all results in one operation.

MERGE and OUTPUT Example: A Step-by-Step Guide

In this example, we’ll look at how to use the OUTPUT clause to log the result of a MERGE operation. We’ll do the following:

1. Create three tables: a source table, a target table, and a log table.
2. We’ll then populate the source and target tables with data.
3. Finally, we’ll perform a MERGE operation to synchronize the source and target tables. We’ll use the OUTPUT clause to log the result of the MERGE operation to our log table.

Create the Source and Target Tables

Here, we’ll create the source table and the target table for the merge operation:

-- Create Target Table
CREATE TABLE TargetTable (
    ID INT PRIMARY KEY,
    Name NVARCHAR(50),
    Quantity INT
);

-- Create Source Table
CREATE TABLE SourceTable (
    ID INT PRIMARY KEY,
    Name NVARCHAR(50),
    Quantity INT
);

-- Create Merge Log Table
CREATE TABLE MergeLog (
    Action NVARCHAR(50),
    OldID INT,
    OldName NVARCHAR(50),
    OldQuantity INT,
    NewID INT,
    NewName NVARCHAR(50),
    NewQuantity INT
);

Populate the Tables

Next, we’ll populate the target and source tables with sample data (but not the merge table):

-- Insert Data into Target Table
INSERT INTO TargetTable (ID, Name, Quantity)
VALUES 
(1, 'Product A', 50),
(2, 'Product B', 30),
(3, 'Product C', 20);

-- Insert Data into Source Table
INSERT INTO SourceTable (ID, Name, Quantity)
VALUES 
(2, 'Product B', 45),  -- Updated record
(3, 'Product C', 20),  -- Same record
(4, 'Product D', 60);  -- New record

Write the MERGE Operation

The MERGE operation will compare the rows in SourceTable with those in TargetTable based on the ID. Depending on whether the row exists or not, the MERGE will either update, insert, or delete rows in the target table.

To capture the changes made during the MERGE, we’ll add the OUTPUT clause. We’ll use this clause to insert into the MergeLog table, the following:

• Any inserted rows
• Any deleted rows
• Both the old and new values for rows that were updated.

OK, let’s go…

MERGE TargetTable AS target
USING SourceTable AS source
ON target.ID = source.ID

WHEN MATCHED THEN
    UPDATE SET target.Name = source.Name, 
               target.Quantity = source.Quantity

WHEN NOT MATCHED BY TARGET THEN
    INSERT (ID, Name, Quantity) 
    VALUES (source.ID, source.Name, source.Quantity)

WHEN NOT MATCHED BY SOURCE THEN
    DELETE

-- Use the OUTPUT clause to log the changes in the MergeLog table
OUTPUT 
    $action AS Operation,
    DELETED.ID AS OldID, 
    DELETED.Name AS OldName, 
    DELETED.Quantity AS OldQuantity,
    INSERTED.ID AS NewID, 
    INSERTED.Name AS NewName, 
    INSERTED.Quantity AS NewQuantity
    INTO MergeLog;

-- Check the merged data
SELECT * FROM MergeLog;

Output:

Action  OldID  OldName    OldQuantity  NewID  NewName    NewQuantity
------  -----  ---------  -----------  -----  ---------  -----------
DELETE  1      Product A  50           null   null       null       
UPDATE  2      Product B  30           2      Product B  45         
UPDATE  3      Product C  20           3      Product C  20         
INSERT  null   null       null         4      Product D  60         

Explanation of the OUTPUT clause:

• $action: Returns the type of operation performed (INSERT, UPDATE, or DELETE).
• DELETED: Captures the old values (before update or deletion).
• INSERTED: Captures the new values (after the insert or update).
• INTO: Specifies a table or table variable to insert the OUTPUT data into. In this case we specified a table.

Practical Applications of MERGE and OUTPUT

Some examples of where the OUTPUT clause could come in handy during a MERGE operation include:

• Change tracking: We can track every change made during a data synchronization process, which is useful for auditing and debugging.
• Historical tables: We can use the OUTPUT clause to store old data in an archive table for future reference.
• Error handling: We can validate the results and use the OUTPUT data to rollback in case of any issues during the operation.