一个从T-SQL MERGE语句中获取insert, update, delete 行数的脚本

set nocount on;

create table #t
(
    id int
,    name varchar(max)
);
go

insert #t values (1,'Jim'),(2,'Sarah'),(3,'Hels');
go

DECLARE @rowcounts TABLE
(
    mergeAction nvarchar(10)
);
declare @insertCount int, @updateCount int, @deleteCount int;
merge into #t as tgt
using (    select 1 as id, 'James' as name
        union
        select 2, 'Sarah'
        union
        select 3, 'Helen'
        union
        select 4, 'Jack'
        union
        select 5, 'Annie') as src
on    tgt.id = src.id
when matched and tgt.name = src.name 
        THEN DELETE
when matched and tgt.name <> src.name
        THEN UPDATE SET tgt.name = src.name
when not matched
        THEN insert values (src.id, src.name)
OUTPUT $action into @rowcounts;

select    @insertcount=[INSERT]
,        @updatecount=[UPDATE]
,        @deletecount=[DELETE]
from    (
        select    mergeAction,1 rows
        from    @rowcounts
        )p
pivot
(
    count(rows)
FOR    mergeAction IN 
(    [INSERT], [UPDATE], [DELETE])
) as pvt
;
drop table #t;


print    '@insertcount = ' + cast(@insertcount as varchar);
print    '@updatecount = ' + cast(@updatecount as varchar);
print    '@deletecount = ' + cast(@deletecount as varchar);