Output the Results 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)