Capturing Insert and Update Counts from Merge
This post shows hows how you can capture and store the number of records inserted, updated or deleted from a T-SQL Merge statement.
This is in response to a question on an earlier post about using Merge to load SCDs in a Data Warehouse.
You can achieve this by using the OUTPUT clause of a merge statement, including the $Action column that OUTPUT returns.
The basic syntax is:
INSERT INTO XXXX SELECT [Action] FROM ( MERGE XXXX AS Target USING XXXX AS Source ON XXXX=XXXX WHEN MATCHED AND XXXX <> XXXX THEN UPDATE SET XXXX=XXXX WHEN NOT MATCHED THEN INSERT ( XXXX ) VALUES ( XXXX ) OUTPUT $action AS [Action] ) MergeOutput
You wrap the Merge statement up as a sub-query, adding the OUTPUT clause to return details about what happened in the merge. Note that you can’t just select from this sub-query, there has to be an INSERT INTO statement.
One row will be returned for each row touched by the merge process.
The $action column will contain either INSERT, UPDATE or DELETE, to indicate what happened to that row.
You can also include Source.* in order to include the source column values in the output dataset.
You can also include DELETED.*, which returns the values of any updated records before they were updated, and INSERTED.* to show the values after the updated. In reality the records are not deleted or inserted, just updated, but DELETED/INSERTED is used as the terminology for old/new values either side of the update. When inserting a new record, DELETED values will be NULL.
... OUTPUT $action AS [Action], Source.*, DELETED.*, INSERTED.* ) MergeOutput
You can then refer to this ‘MergeOutput’ result set at the top of the query by selecting from this sub-query.
There is a limitation, though, you can’t aggregate the table. So if we want to summarise the actions into a single row of insert, update and delete counts, we have to use a temporary table such as in the sample code below.
CREATE TABLE #MergeActions ([Action] VARCHAR(10)) INSERT INTO #MergeActions ([Action]) SELECT [Action] FROM ( MERGE [dim].[Date] AS Target USING [etl].[DimDate] AS Source ON ISNULL(Target.[DateKey],0) = ISNULL(Source.[DateKey],0) WHEN MATCHED AND (Target.[Date] <> Source.[Date] OR Target.[Month] <> Source.[Month] OR Target.[Quarter] <> Source.[Quarter] OR Target.[Year] <> Source.[Year] ) THEN UPDATE SET [Date] = Source.[Date] ,[Month] = Source.[Month] ,[Quarter] = Source.[Quarter] ,[Year] = Source.[Year] ,LastUpdated = GetDate() WHEN NOT MATCHED THEN INSERT ( [DateKey] ,[Date] ,[Month] ,[Quarter] ,[Year] ,LastUpdated ) VALUES ( Source.[DateKey] ,Source.[Date] ,Source.[Month] ,Source.[Quarter] ,Source.[Year] ,GetDate() ) OUTPUT $action AS [Action] ) MergeOutput ; SELECT SUM(CASE WHEN [Action]='INSERT' THEN 1 ELSE 0 END) AS InsertCount ,SUM(CASE WHEN[Action]='UPDATE' THEN 1 ELSE 0 END) AS UpdateCount ,SUM(CASE WHEN [Action]='DELETE' THEN 1 ELSE 0 END) AS DeleteCounts FROM #MergeActions GROUP BY [Action] DROP TABLE #MergeActions