Sql Server 2008 MERGE – best way to get counts

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