How do you use the OUTPUT clause with the MERGE statement to track changes?
BobHarris
Posted by BobHarris
Last Updated: June 27, 2024
OUTPUT MERGE
The OUTPUT clause in SQL Server can be quite useful in conjunction with the MERGE statement when you want to capture changes made to the rows in a target table during the INSERT, UPDATE, or DELETE operations. This is especially useful for auditing or logging purposes. Here's a general overview of how to use the OUTPUT clause with the MERGE statement:
Syntax Overview
The basic syntax for a MERGE statement with the OUTPUT clause looks like this: 
MERGE [TargetTable] AS target
USING [SourceTable] AS source
ON target.KeyColumn = source.KeyColumn
WHEN MATCHED THEN 
    UPDATE SET target.Column1 = source.Column1
WHEN NOT MATCHED BY TARGET THEN 
    INSERT (Column1, Column2) VALUES (source.Column1, source.Column2)
WHEN NOT MATCHED BY SOURCE THEN 
    DELETE
OUTPUT $action, inserted.*, deleted.* INTO [YourOutputTable];
Components Explained
1. MERGE Statement: - This combines the actions of UPDATE, INSERT, and DELETE based on the results of a comparison between the TargetTable and a SourceTable. 2. OUTPUT Clause: - The OUTPUT clause allows you to return information from the rows that were affected by the MERGE operation. - You can access both the inserted and deleted logical tables within the OUTPUT clause. 3. Actions: - $action identifies the type of action that was performed: INSERT, UPDATE, or DELETE. - inserted refers to the new state of the rows after the INSERT or UPDATE. - deleted refers to the state of the rows before they were modified by the UPDATE or DELETE. 4. INTO Clause: - You can specify an INTO clause to direct the output results into a specified table (e.g., YourOutputTable), which you would need to have created beforehand.
Example
Here’s an example that demonstrates this: 
-- Example tables
CREATE TABLE TargetTable (ID INT PRIMARY KEY, Value NVARCHAR(100));
CREATE TABLE SourceTable (ID INT, Value NVARCHAR(100));
CREATE TABLE ChangeLog (ChangeType NVARCHAR(10), ID INT, OldValue NVARCHAR(100), NewValue NVARCHAR(100));

-- Sample data
INSERT INTO TargetTable (ID, Value) VALUES (1, 'Old Value 1'), (2, 'Old Value 2');
INSERT INTO SourceTable (ID, Value) VALUES (1, 'Updated Value 1'), (3, 'New Value 3');

-- Merge with OUTPUT clause
MERGE TargetTable AS target
USING SourceTable AS source
ON target.ID = source.ID
WHEN MATCHED THEN 
    UPDATE SET target.Value = source.Value
WHEN NOT MATCHED BY TARGET THEN 
    INSERT (ID, Value) VALUES (source.ID, source.Value)
WHEN NOT MATCHED BY SOURCE THEN 
    DELETE
OUTPUT $action, deleted.ID, deleted.Value AS OldValue, inserted.Value AS NewValue 
INTO ChangeLog (ChangeType, ID, OldValue, NewValue);
Explanation of the Example
1. Setup: Three tables are created: TargetTable to hold the main data, SourceTable for incoming changes, and ChangeLog to track what changes were made. 2. Data Insertion: Initial sample data is added to both TargetTable and SourceTable. 3. Merge Operation: The MERGE statement compares TargetTable and SourceTable. Depending on the match: - If a record is found in both tables (MATCHED), it updates the TargetTable. - If a record is only found in the SourceTable (NOT MATCHED BY TARGET), it inserts that record into the TargetTable. - If a record exists in TargetTable but not in SourceTable (NOT MATCHED BY SOURCE), it deletes that record. 4. Output Logging: The OUTPUT clause captures the actions taken, the IDs of the affected records, and their old and new values, inserting this information into the ChangeLog. This method of auditing changes is efficient and leverages the MERGE capabilities effectively.
Default Image