0845 643 64 63

Capturing Insert and Update Counts from Merge

This post 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:

 
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.

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.* and/or Target.* in order to include the source or target column values in the output dataset.

 ... OUTPUT $action AS [Action], Source.*, Target.*
) 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 

</Frog-Blog Out>

Power BI Sentinel

We all know and love Power BI, because of it’s power and ease of use, there’s a good reason why it’s number 1 in the Gartner Quadrant. But how […] Continue Reading…

What is U-SQL?

By now you may have heard about U-SQL, the new kid on the query language block. But what is U-SQL? Where is it? What’s it for?

I was lucky enough […] Continue Reading…

1 2 3 11
Power BI Sentinel

The Frog Blog

Team Purple Frog specialise in designing and implementing Microsoft Data Analytics solutions, including Data Warehouses, Cubes, SQL Server, SSIS, ADF, SSAS, Power BI, MDX, DAX, Machine Learning and more.

This is a collection of thoughts, ramblings and ideas that we think would be useful to share.

Authors:

Alex Whittles
(MVP)
Reiss McSporran
Jeet Kainth
Jon Fletcher

Data Platform MVP

Power BI Sentinel
Frog Blog Out
twitter
rssicon