MERGE Stats

The new SQL Server Merge statements is super cool. I needed a good way to get the stats back from an executed merge statement to tell me how many rows were affected by each action type, so I posted to the SQL Server Programming newsgroup and got this response back from Plamen Ratchev . Thanks Plamen:

DECLARE @counts TABLE(action VARCHAR(20));

MERGE INTO Foo1 AS A
USING Foo2 AS B
ON A.keycol = B.keycol
WHEN MATCHED
THEN UPDATE SET datacol = B.datacol
WHEN NOT MATCHED
THEN INSERT (keycol, datacol) VALUES(B.keycol, B.datacol)
WHEN NOT MATCHED BY SOURCE
THEN DELETE
OUTPUT $action INTO @counts;

SELECT action, COUNT(*) AS cnt
FROM @counts
GROUP BY action;

/*

Results:

action cnt
-------------------- -----------
DELETE 2
INSERT 1
UPDATE 2

*/