This example demonstrates how to output the performed actions of a SQL Server merge statement.
-- ---------------------------------------------------------------------------
-- Create target and source tables and populate with data
-- ---------------------------------------------------------------------------
CREATE TABLE Products (ProductID INT PRIMARY KEY, ProductName VARCHAR(100), Rate MONEY);
GO
CREATE TABLE UpdatedProducts (ProductID INT PRIMARY KEY, ProductName VARCHAR(100), Rate MONEY);
GO
INSERT INTO Products
VALUES (1, 'Tea', 10.00), (2, 'Coffee', 20.00), (3, 'Muffin', 30.00), (4, 'Biscuit', 40.00);
GO
INSERT INTO UpdatedProducts
VALUES (1, 'Tea', 10.00), (2, 'Coffee', 25.00), (3, 'Muffin', 35.00), (5, 'Pizza', 60.00);
GO
-- ---------------------------------------------------------------------------
-- Synchronize the target table with refreshed data from source table
-- ---------------------------------------------------------------------------
MERGE Products AS TARGET
USING UpdatedProducts AS SOURCE
ON (TARGET.ProductID = SOURCE.ProductID)
-- When records are matched, update the records if there is any change
WHEN MATCHED
AND TARGET.ProductName <> SOURCE.ProductName
OR TARGET.Rate <> SOURCE.Rate
THEN UPDATE SET TARGET.ProductName = SOURCE.ProductName, TARGET.Rate = SOURCE.Rate
-- When no records are matched, insert the incoming records from source table to target table
WHEN NOT MATCHED BY TARGET
THEN INSERT (ProductID, ProductName, Rate)
VALUES (SOURCE.ProductID, SOURCE.ProductName, SOURCE.Rate)
-- When there is a row that exists in target and same record does not exist in
-- source then delete this record target
WHEN NOT MATCHED BY SOURCE
THEN DELETE
-- $action specifies a column of type nvarchar(10) in the OUTPUT clause that
-- returns one of three values for each row: 'INSERT', 'UPDATE', or 'DELETE'
-- according to the action that was performed on that row.
OUTPUT $action,
DELETED.ProductID AS TargetProductID,
DELETED.ProductName AS TargetProductName,
DELETED.Rate AS TargetRate,
INSERTED.ProductID AS SourceProductID,
INSERTED.ProductName AS SourceProductName,
INSERTED.Rate AS SourceRate;
-- When used after MERGE, @@ROWCOUNT returns the total number of rows inserted,
-- updated, and deleted to the client.
SELECT @@ROWCOUNT;
GO
-- ---------------------------------------------------------------------------
-- Result output
-- ---------------------------------------------------------------------------
-- | $action | TargetProductID | TargetProductName | TargetRate | SourceProductID | SourceProductName | SourceRate |
-- |---------|-----------------|-------------------|------------|-----------------|-------------------|------------|
-- | UPDATE | 2 | Coffee | 20.00 | 2 | Coffee | 25.00 |
-- | UPDATE | 3 | Muffin | 30.00 | 3 | Muffin | 35.00 |
-- | DELETE | 4 | Biscuit | 40.00 | NULL | NULL | NULL |
-- | INSERT | NULL | NULL | NULL | 5 | Pizza | 60.00 |
--
-- 4 (@@ROWCOUNT)