How to Tell Whether a Given Row was Inserted, Updated, or Deleted During a MERGE in SQL Server

If you’re using the MERGE statement in SQL Server to merge data between tables, you may or may not be aware that the statement gives us the ability to check which rows were affected by the MERGE operation, as well as how they were affected.

To get this info, we can use the OUTPUT clause along with the $action argument.

About the `OUTPUT` Clause

The (optional) 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).

When doing a MERGE operation, we can use this clause to get a combination of inserted, deleted, and updated rows (depending on which operation was performed on each row).

About the `$action` Argument

The $action argument can be used with the OUTPUT clause in order to tell us whether the operation resulted in an insert, an update, or a delete for the given row. It returns a varchar(10) with the action that was performed during the merge (INSERT, UPDATE, or DELETE).

The way it works is that, the OUTPUT clause returns each row that was affected by the operation. We can get the actual values when we do this (for example, the old and new values in the case of an UPDATE). But when we’re doing a merge, we can also use $action to tell us whether it was an INSERT, UPDATE, or DELETE operation.

The $action argument is exclusive to MERGE operations (it can’t be used on other operations like INSERT, or UPDATE, or DELETE).

Example of `MERGE` with the `$action` Argument

This is probably one case where an example is better than words. If you’re struggling to understand my explanation of the $action argument, it should all become clear once you see the result of the following example.

Setup: Create Sample Tables

Suppose we create the following source and target tables and insert data:

-- 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
);

-- 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

We’ll use this for our MERGE operation.

The Merge Operation

Now let’s perform a MERGE operation. We’ll use the OUTPUT clause to output the results of the operation:

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 return the result of the MERGE
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;

Output:

Operation  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

We can see all the details of the merge. Importantly, we can see whether a row was inserted, updated, or deleted. We can see that because we used the $action argument (first column).

As mentioned, the $action argument is only available for MERGE operations.

Output the Changes into a Table

You might be thinking that the information provided by the OUTPUT clause and its associated $action argument would be invaluable to have, if only we could save that data somewhere.

Well I’ve got good news for you! We can insert the result of the OUTPUT clause into a table or a table variable if required. And even better news; it’s very straightforward to do.

For example, we could create a log table like this:

CREATE TABLE MergeLog (
    Action NVARCHAR(50),
    OldID INT,
    OldName NVARCHAR(50),
    OldQuantity INT,
    NewID INT,
    NewName NVARCHAR(50),
    NewQuantity INT
);

That contains all the columns required to store the result of our OUTPUT statement, including the $action argument.

We can now update our OUTPUT statement to include INTO MergeLog:

-- Do the merge operation
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;

-- Get the results from the log table
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

We can see that the table contains the same result that was returned in the previous example.

Note that I dropped all tables and started again before I ran this example in order to demonstrate that the same data was stored in the table.

About the OUTPUT Clause

About the $action Argument

Example of MERGE with the $action Argument