THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Jamie Thomson

This is the blog of Jamie Thomson, a freelance data mangler in London

Extracting insert, update, delete rowcounts from T-SQL MERGE

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:

image

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

Published Sunday, August 30, 2009 1:54 AM by jamiet
Filed under: , ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Peso said:

Have you tried wrap the MERGE statement as a COMPOSABLE DML?

That way, you only have to insert the aggregated values into @rowcounts.

August 30, 2009 3:08 AM
 

jamiet said:

Hi Peso,

I have tried it, yeah. I got an error that I certainly wasn't expecting however: "The GROUP BY clause is not allowed when the FROM clause contains a nested INSERT, UPDATE, DELETE, or MERGE statement."

So yes, I can use composable DML to get a the contents of the OUTPUT clause but it seems I cannot aggregate them prior to the insertion. That's rather annoying.

-Jamie

P.S. Here's the code that throws that error if you care to run it:

set nocount on ;

--drop table #t

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)

, numRows int

);

declare @insertCount int, @updateCount int, @deleteCount int;

insert @rowcounts

select x.mergeAction,COUNT(*)

from (

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 as mergeAction

) x

group by x.mergeAction;

August 30, 2009 5:50 PM
 

Roji Thomas said:

The final selct can be simplified as

SELECT @insertcount=  SUM(CASE WHEN mergeAction = 'INSERT' THEN 1 ELSE 0 END),

   @updatecount = SUM(CASE WHEN mergeAction = 'UPDATE' THEN 1 ELSE 0 END),

   @deletecount  = SUM(CASE WHEN mergeAction = 'DELETE' THEN 1 ELSE 0 END)

FROM @rowcounts    

August 31, 2009 9:17 AM
 

Fotis said:

perfect! exactly what i was looking for!!!

July 10, 2010 9:27 AM
 

MartinIsti said:

Jamie, I've been using your way of counting the rows when it comes to a MERGE in SSIS for logging and audit reasons but today I encountered the same problem for the second time (at first I thought I solved it but not).

The issue is that when e.g. I execute the ETL in DEV multiple times after the first run the MERGE would do nothing. No INSERT, no UPDATE, no DELETE. That means no result. Not NULL, not 0 but no rows returned.

This first gave an error that SSIS cannot assign the non-existing value to a rowcount variable. OK I put a UNION in there and thought it would handle it. It did in a way but not as I wanted. There always was a result but never the correct counts.

Today I corrected it (maybe not for the last time):

...

OUTPUT $action into @rowcounts;

select

     *

from    (

     select mergeAction,     1 rows        from    @rowcounts

     union all

     select 'nothing' as mergeAction, 0 as rows

         )p

     pivot

         (    count(rows) FOR    mergeAction IN ( [INSERT], [UPDATE], [nothing])) as pvt;

-----------------------------------------------

I'm not 100% sure that it works in all the possible cases but it seems to be alright after my initial tests. Of course I don't use the [nothing] count at all it's just there helping avoid the case when no rows are returned.

Any comments are welcome related to this small 'addon feature'. You all know how picky SSIS can be :)

November 11, 2010 10:29 PM
 

jamiet said:

Roji Thomas,

Thanks for that. Note, for anyone using Roji's technique, that you would need to wrap that with ISNULLs:

SELECT @insertcount=  ISNULL(SUM(CASE WHEN mergeAction = 'INSERT' THEN 1 ELSE 0 END),0),

  @updatecount = ISNULL(SSUM(CASE WHEN mergeAction = 'UPDATE' THEN 1 ELSE 0 END),0),

  @deletecount  = ISNULL(SSUM(CASE WHEN mergeAction = 'DELETE' THEN 1 ELSE 0 END),0)

FROM @rowcounts

Note that the pivot method doesn't require any ISNULL.

JT

May 11, 2011 10:25 AM
 

Rishit said:

Worked flawlessly for me.

Exactly what the Dr has prescribed to me.

Thanks

May 22, 2012 10:11 AM
 

Lamprey said:

I know this is an old thread, but just incase someone else comes across it, there is no need to pivot or use coalesce(isnull):

   SELECT

       @InsertCount = COUNT_BIG(IIF(MergeAction = 'INSERT', 1, NULL))

       ,@UpdateCount = COUNT_BIG(IIF(MergeAction = 'UPDATE', 1, NULL))

       ,@DeleteCount = COUNT_BIG(IIF(MergeAction = 'DELETE', 1, NULL))

   FROM

       @RowCount

August 17, 2012 12:54 PM
 

Alexandr said:

Thank you, Jamie. This article was very useful for me!

March 18, 2013 8:54 AM
 

Karim said:

Cool article. Thanks a lot!!!

November 18, 2013 4:40 PM
 

Amar said:

Why union or pivot or add an integer?

output $action into @rowcounts;

select mergeAction,count(mergeAction) as rows from @rowcounts group by mergeAction

March 16, 2014 9:19 AM
 

Jester said:

@Amar: To set value of variables @insertcount, @updatecount, and @deletecount accordingly. These would be more useful as output parameters in a stored procedure.

April 23, 2014 10:11 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement