It was almost a year ago, but from the looks of things I just wanted to show composable DML working. It does offer an opportunity to do more interesting things in the outer INSERT, but you certainly could use a straight OUTPUT INTO in the code presented previously:

DECLARE @s-2 TABLE (id integer PRIMARY KEY, val integer)

DECLARE @T TABLE (id integer PRIMARY KEY, val integer)

INSERT @s-2 VALUES (1, 5), (2, 8), (3, 1), (4, 5), (5,9)

INSERT @T VALUES (1, 1), (2, 2)

DECLARE @C TABLE (act tinyint)

MERGE @T AS t

USING @s-2 AS s ON s.id = t.id

WHEN NOT MATCHED THEN

INSERT VALUES (s.id, s.val)

WHEN MATCHED AND t.val <> s.val THEN

UPDATE SET t.val = s.val

WHEN MATCHED THEN DELETE

OUTPUT

CASE

WHEN $action = N'UPDATE' THEN CONVERT(TINYINT, 1)

WHEN $action = N'DELETE' THEN CONVERT(TINYINT, 3)

WHEN $action = N'INSERT' THEN CONVERT(TINYINT, 4)

END INTO @C;

SELECT

act =

CASE c.act

WHEN 1 THEN 'Update'

WHEN 3 THEN 'Delete'

WHEN 4 THEN 'Insert'

END,

cnt = COUNT_BIG(*)

FROM @C AS c

GROUP BY

c.act;