TSQL: Capturing Changes with MERGE and Logging OUTPUT INTO Regular Table, Temp Table, or Table Variable

The MERGE statement in T-SQL is used to perform an UPSERT operation, which means it can insert, update, or delete rows in a target table based on the data provided from a source table or query. The OUTPUT clause in a MERGE statement allows SQL developers to capture the affected rows and their before/after values.

In this article, we will look at an example that will fully illustrate how to use the MERGE statement with the OUTPUT clause.

Sample Data to Illustrate Using the MERGE statement with the OUTPUT clause

You can use this Fiddle to follow along and practice MERGE statement with OUTPUT clause

Create Customers and CustomerUpdates tables to demonstrate using MERGE with OUTPUT
-- Create Customers table. This will be the TARGET table for MERGE.
CREATE TABLE Customers
(
    CustomerID INT PRIMARY KEY,
    Name NVARCHAR(100),
    Email NVARCHAR(100)
);

-- Insert sample data
INSERT INTO Customers
(
    CustomerID,
    Name,
    Email
)
VALUES
(1, 'John Smith', 'john@example.com'),
(2, 'Jane Doe', 'jane@example.com'),
(3, 'Mark Brown', 'mark@example.com');

-- Create CustomerUpdates table. This will be the SOURCE table for MERGE.
CREATE TABLE CustomerUpdates
(
    CustomerID INT PRIMARY KEY,
    Name NVARCHAR(100),
    Email NVARCHAR(100)
);

-- Insert sample data
INSERT INTO CustomerUpdates
(
    CustomerID,
    Name,
    Email
)
VALUES
(1, 'John Johnson', 'john.j@example.com'),
(3, 'Mark Brown', 'updated_mark@example.com'),
(4, 'Emily White', 'emily@example.com');

Customers Table:

This will be the TARGET table for the MERGE statement below.

CustomerID	Name	Email
1	John Smith	john@example.com
2	Jane Doe	jane@example.com
3	Mark Brown	mark@example.com

CustomerUpdates Table:

This will be the SOURCE table for the MERGE statement below.

CustomerID	Name	Email
1	John Johnson	john.j@example.com
3	Mark Brown	updated_mark@example.com
4	Emily White	emily@example.com

Use the MERGE Statement with an OUTPUT Clause to Log Changes into a Physical Table

Output merge logs into a physical table
-- Create CustomerUpdatesLog table
CREATE TABLE CustomerUpdatesLog
(
    CustomerUpdatesLogID INT IDENTITY(1, 1) PRIMARY KEY,
    Action NVARCHAR(10),
    CustomerID INT,
    Name NVARCHAR(100),
    Email NVARCHAR(100),
    NewName NVARCHAR(100),
    NewEmail NVARCHAR(100),
    ChangeTimestamp DATETIME
);

-- MERGE CustomerUpdates table into Customers table
MERGE Customers AS TARGET
USING CustomerUpdates AS SOURCE
ON TARGET.CustomerID = SOURCE.CustomerID
WHEN MATCHED THEN
    UPDATE SET TARGET.Name = SOURCE.Name,
               TARGET.Email = SOURCE.Email
WHEN NOT MATCHED THEN
    INSERT
    (
        CustomerID,
        Name,
        Email
    )
    VALUES
    (SOURCE.CustomerID, SOURCE.Name, SOURCE.Email)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE
OUTPUT $action,
       Deleted.CustomerID,
       Deleted.Name,
       Deleted.Email,
       Inserted.Name,
       Inserted.Email,
       GETDATE()
INTO CustomerUpdatesLog;

-- Display records in Customers table after MERGE
SELECT *
FROM Customers;

-- Display logs outputted by MERGE statement
SELECT *
FROM CustomerUpdatesLog;

Customers Table:

The data in the Customers table after the MERGE operations.

CustomerID	Name	Email
1	John Johnson	john.j@example.com
3	Mark Brown	updated_mark@example.com
4	Emily White	emily@example.com

CustomerUpdatesLog Table:

The audit log of changes made by the MERGE operations.

CustomerUpdatesLogID	Action	CustomerID	Name	Email	NewName	NewEmail	ChangeTimestamp
1	UPDATE	1	John Smith	john@example.com	John Johnson	john.j@example.com	2023-08-16 16:10:16.923
2	DELETE	2	Jane Doe	jane@example.com	null	null	2023-08-16 16:10:16.923
3	UPDATE	3	Mark Brown	mark@example.com	Mark Brown	updated_mark@example.com	2023-08-16 16:10:16.923
4	INSERT	null	null	null	Emily White	emily@example.com	2023-08-16 16:10:16.923

The SQL code snippet above first creates a table called CustomerUpdatesLog with the following columns:

CustomerUpdatesLogID: An identity column serving as the primary key for the log entries.
Action: Indicates the action performed (INSERT, UPDATE, or DELETE).
CustomerID: Stores the ID of the customer affected by the action.
Name: Stores the name of the customer before the action.
Email: Stores the email of the customer before the action.
NewName: Stores the new name of the customer after an update (NULL for other actions).
NewEmail: Stores the new email of the customer after an update (NULL for other actions).
ChangeTimestamp: Records the timestamp when the change occurred.

The code uses MERGE statement with an OUTPUT clause to track changes and populate an audit log table CustomerUpdatesLog.

This MERGE statement performs the following actions:

Updates existing records in the Customers table if there's a match based on CustomerID.
Inserts new records into the Customers table if there's no match.
Deletes records from the Customers table if they don't have a match in the source CustomerUpdates table.
The OUTPUT clause captures information about the changes, including the action ($action), old and new values, and the timestamp of the change. This information is then inserted into the CustomerUpdatesLog table.

The two SELECT statements at the end retrieve data from the Customers table (after the MERGE operations) and the CustomerUpdatesLog table (audit log of changes).

This example demonstrates how to use the MERGE statement along with an OUTPUT clause to track changes made to a table and store them in an audit log. It's a great way to maintain a historical record of data modifications in your database.

Use the MERGE Statement with an OUTPUT Clause to Log Changes into a Temporary Table

If you don't have permission to create a physical table in your environment, you can simply log changes into a temporary table as well.

Output merge logs into a temporary table
-- Create #CustomerUpdatesLog temporary table
CREATE TABLE #CustomerUpdatesLog
(
    CustomerUpdatesLogID INT IDENTITY(1, 1) PRIMARY KEY,
    Action NVARCHAR(10),
    CustomerID INT,
    Name NVARCHAR(100),
    Email NVARCHAR(100),
    NewName NVARCHAR(100),
    NewEmail NVARCHAR(100),
    ChangeTimestamp DATETIME
);

-- MERGE CustomerUpdates table into Customers table
MERGE Customers AS TARGET
USING CustomerUpdates AS SOURCE
ON TARGET.CustomerID = SOURCE.CustomerID
WHEN MATCHED THEN
    UPDATE SET TARGET.Name = SOURCE.Name,
               TARGET.Email = SOURCE.Email
WHEN NOT MATCHED THEN
    INSERT
    (
        CustomerID,
        Name,
        Email
    )
    VALUES
    (SOURCE.CustomerID, SOURCE.Name, SOURCE.Email)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE
OUTPUT $action,
       Deleted.CustomerID,
       Deleted.Name,
       Deleted.Email,
       Inserted.Name,
       Inserted.Email,
       GETDATE()
INTO CustomerUpdatesLog;

-- Display records in Customers table after MERGE
SELECT *
FROM Customers;

-- Display logs outputted by MERGE statement
SELECT *
FROM #CustomerUpdatesLog;

Use the MERGE Statement with an OUTPUT Clause to Log Changes into a Table Variable

Alternatively, you can log changes into a table varilable if you don't have permission to create a physical table in your environment.

Output merge logs into a table variable
-- Create @CustomerUpdatesLog table variable
DECLARE @CustomerUpdatesLog TABLE
(
    CustomerUpdatesLogID INT IDENTITY(1, 1) PRIMARY KEY,
    Action NVARCHAR(10),
    CustomerID INT,
    Name NVARCHAR(100),
    Email NVARCHAR(100),
    NewName NVARCHAR(100),
    NewEmail NVARCHAR(100),
    ChangeTimestamp DATETIME
);

-- MERGE CustomerUpdates table into Customers table
MERGE Customers AS TARGET
USING CustomerUpdates AS SOURCE
ON TARGET.CustomerID = SOURCE.CustomerID
WHEN MATCHED THEN
    UPDATE SET TARGET.Name = SOURCE.Name,
               TARGET.Email = SOURCE.Email
WHEN NOT MATCHED THEN
    INSERT
    (
        CustomerID,
        Name,
        Email
    )
    VALUES
    (SOURCE.CustomerID, SOURCE.Name, SOURCE.Email)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE
OUTPUT $action,
       Deleted.CustomerID,
       Deleted.Name,
       Deleted.Email,
       Inserted.Name,
       Inserted.Email,
       GETDATE()
INTO CustomerUpdatesLog;

-- Display records in Customers table after MERGE
SELECT *
FROM Customers;

-- Display logs outputted by MERGE statement
SELECT *
FROM @CustomerUpdatesLog;

Sample Data to Illustrate Using the MERGE statement with the OUTPUT clause​

Use the MERGE Statement with an OUTPUT Clause to Log Changes into a Physical Table​

Use the MERGE Statement with an OUTPUT Clause to Log Changes into a Temporary Table​

Use the MERGE Statement with an OUTPUT Clause to Log Changes into a Table Variable​

Sample Data to Illustrate Using the MERGE statement with the OUTPUT clause

Use the MERGE Statement with an OUTPUT Clause to Log Changes into a Physical Table

Use the MERGE Statement with an OUTPUT Clause to Log Changes into a Temporary Table

Use the MERGE Statement with an OUTPUT Clause to Log Changes into a Table Variable