Sql Server 2008 MERGE - best way to get counts

Question

I'm wondering what y'alls would recommend as the best way to go about getting the action counts from a MERGE statement in Sql Server.

So, i.e. I run a MERGE which does some inserts, some updates and some deletes, ... I would like to be able to find out HOW MANY inserts, HOW MANY updates and How Many deletes.

What'd be the best way to do this?

Answer 1

You could specify an OUTPUT clause on your MERGE statement and get an output report of what's been done during MERGE.

MERGE (targetTable) AS t  USING (sourceTable) AS s ON t.ID = s.ID WHEN MATCHED THEN   (some statements) WHEN NOT MATCHED THEN   (some statements) OUTPUT   $action, inserted.ID 'inserted', deleted.ID 'deleted' ; 

This will give you a row for each "action" (insert, update, delete) for each operation. If it's a lot of statements, you could also OUTPUT INTO @tableVar and then look at the table variable.

DECLARE @tableVar TABLE (MergeAction VARCHAR(20), InsertedID INT, DeletedID INT)  MERGE (targetTable) AS t  USING (sourceTable) AS s ON t.ID = s.ID WHEN MATCHED THEN       (some statements) WHEN NOT MATCHED THEN       (some statements) OUTPUT       $action, inserted.ID 'inserted', deleted.ID 'deleted' INTO @tableVar ;  SELECT MergeAction, COUNT(*)  FROM @tableVar   GROUP BY MergeAction 

Check out the Books Online for details on the MERGE statement and the OUTPUT clause.

Marc

Answer 2

To extract into individual vars, can post process answer by marc_s using pivot:

    declare         @mergeResultsTable table (MergeAction VARCHAR(20));      declare         @insertCount int,         @updateCount int,         @deleteCount int;      merge ...     output $action into @mergeResultsTable;       select @insertCount = [INSERT],            @updateCount = [UPDATE],            @deleteCount = [DELETE]       from (select 'NOOP' MergeAction -- row for null merge into null              union all             select * from @mergeResultsTable) mergeResultsPlusEmptyRow           pivot (count(MergeAction)         for MergeAction in ([INSERT],[UPDATE],[DELETE]))          as mergeResultsPivot; 

The union 'noop' row can be removed if init vars to 0 or know that source or target table has >0 rows.