Best ways to log action by merge statement, potentially with ability to identify error in TSQL

Question

I use Merge statement to perform table population, I am looking for a best way to implement error logging for the merge statement.

Initially, I want to log the number of rows that are affected by actions (e.g. insert, update, delete action) performed by merge statement, rather than detailed row contents. However, the logged details might not be enough for identifying cause of error, should error have risen.

The second option is to log all the rows that Merge carried out into a log table. However, the downside is that one logging table is required for each business table; and to keep all the data.

However, because merge performs atomic operations, which means there is no data to log when error occurred. Thus, the error logging function is probably used for tracking potential bug in the table population logic (please correct it if it is wrong).

I want a way to log error during the merge statement, so I can identify error, or even recover data.

Any advice would be very much appreciated.

To save the result of Merge, use Merge OUTPUT INTO to insert all affected row into a table, and the @@RowCount, below:

DECLARE @ChangedTable Table (
Action Varchar(20),
TargetProductID Int,
TargetProductName Varchar(100),
TargetRate Money,
SourceProductID Int, 
SourceProductName Varchar(100), 
SourceRate Money
)

--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 table and
--same record does not exist in source table
--then delete this record from target table
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
INTO @ChangedTable;

select * from @ChangedTable;

SELECT @@ROWCOUNT;

Any idea would be very much appreciated.

Tables:

--Create a target table
CREATE TABLE Products
(
ProductID INT PRIMARY KEY,
ProductName VARCHAR(100),
Rate MONEY
) 
GO
--Insert records into target table
INSERT INTO Products
VALUES
(1, 'Tea', 10.00),
(2, 'Coffee', 20.00),
(3, 'Muffin', 30.00),
(4, 'Biscuit', 40.00)
GO
--Create source table
CREATE TABLE UpdatedProducts
(
ProductID INT PRIMARY KEY,
ProductName VARCHAR(100),
Rate MONEY
) 
GO
--Insert records into source table
INSERT INTO UpdatedProducts
VALUES
(1, 'Tea', 10.00),
(2, 'Coffee', 25.00),
(3, 'Muffin', 35.00),
(5, 'Pizza', 60.00)
GO
SELECT * FROM Products
SELECT * FROM UpdatedProducts
GO

Answer 1

I want to log the number of rows that are affected by action (e.g. insert, update, delete action) performed by merge statement, rather than detailed row contents.

To do this, you're well on your way!

Basically, all you need to do is change your SELECT from the @ChangedTable to be:

SELECT Action, COUNT(*) 
FROM @ChangedTable
GROUP BY Action;

and you should get something like:

Action  (No column name)
DELETE        1
INSERT        1
UPDATE        2

Is that what you're looking for??