count from merge statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

count from merge statement

Author

Topic

subhaoviya
Posting Yak Master

135 Posts

Posted - 2012-08-28 : 07:55:21

Hi,

I Have merge statement in my stored procedure, which has both Update and insert action. Is it possible to get the count of affected rows after this statement?

my query

Merge [Target] as T
Using [Source] as S
on(S.C1=T.C1)
When Matched then
Update set
T.C2=S.C2
When not matched by target then
insert (c1,c2)
values(s.C1,S.C2)

if 2 rows gets updated and 2 gets inserted in the above statement, i need 5 as count.

Thanks
Subha

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2012-08-28 : 08:46:59

DECLARE @RowCount INT

Merge [Target] as T Using [Source] as S
on( S.C1 = T.C1 ) When Matched then
Update
set T.C2 = S.C2 When not matched by target then
insert ( c1, c2 )
values
( s.C1, S.C2 )

SELECT @RowCount = @@ROWCOUNT

--------------------------
http://connectsql.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources