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

最新推荐文章于 2022-01-31 00:29:07 发布

武哥博文

最新推荐文章于 2022-01-31 00:29:07 发布

阅读量1.2k

点赞数

文章标签： insert delete merge table output action

本文通过一个具体的 SQL Merge 语句示例，展示了如何使用 Merge 语句来更新、插入和删除数据库表中的记录，并同时跟踪这些操作的数量。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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);