Just lately I’ve been using T-SQL’s MERGE statement (introduced in SQL Server 2008) and one thing that I needed to do was extract rowcounts for each DML operation (i.e. INSERT, UPDATE, DELETE) conducted by a MERGE. I was surprised to find that while @@ROWCOUNT is supported for MERGE, it only returns the total number of affected rows and there are no built in functions for getting the counts for each DML operation (although Aaron Bertrand has a Connect submission asking for it).
There is an easy way around this however; MERGE includes a function called $action which can be used in the OUTPUT clause to specify whether a row was inserted, updated or deleted. I cut a small bit of code that demos how it works which you can get at the bottom of this blog post. Here’s what it produces:
I hope its useful to someone!
If you want to learn more about the MERGE statement and the OUTPUT clause then head over and read Adam Machanic’s post Dr OUTPUT: Or how I learned to stop worrying and love the MERGE.
@Jamiet
Here’s the code!! Disclaimer: Take it or leave it – just don’t shout at me if there are any problems here (although constructive comments are welcomed)
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);