cancel
Showing results for 
Search instead for 
Did you mean: 

counting inserts/updates when doing a merge

Former Member
0 Kudos

When doing a merge statement we'd like to log the number of rows inserted and the number of rows updated.

At present we do this using 2 separate statements so we know from @@rowcount how many rows each one has operated on.

Is there anyway of getting the individual counts from a merge statement ?

Thanks

Accepted Solutions (0)

Answers (1)

Answers (1)

kevin_sherlock
Contributor
0 Kudos

Interesting question.  Here is one way I can think to do it:

declare @minserts int, @mupdates int

set @minserts = 0

set @mupdates = 0

merge into myhist as h

    using mytrans as t

      on h.pkey = t.pkey

when matched then update set h.description = t.description, @mupdates = @mupdates + 1

when not matched then insert (pkey,description) values (t.pkey,t.description)

select @minserts = @@rowcount - @mupdates

print "UPDATES:  %1!   INSERTS:  %2!",@mupdates,@minserts

Former Member
0 Kudos

Thanks - that looks good.

Mike

kevin_sherlock
Contributor
0 Kudos

Mike,

I'm glad that works.  If that answers your question, you should go ahead and mark the question as answered.