Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


merge statement - to get a row count


merge statement - to get a row count

Author
Message
sqlfriends
sqlfriends
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2045 Visits: 3857
I use a merge statement to do incremental load to the table.

I use it in a stored procedure that is called in SSIS.

In the merge stagement, there is an update, and also an insert, is there a way for each action I can get a row count, for example row count of how many records are updated, and how many are inserted.

Thanks
Paul White
Paul White
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10338 Visits: 11350
There's no direct way (that I know of), but:


DECLARE @S TABLE (id integer PRIMARY KEY, val integer)
DECLARE @T TABLE (id integer PRIMARY KEY, val integer)

INSERT @S VALUES (1, 5), (2, 8), (3, 1), (4, 5), (5,9)
INSERT @T VALUES (1, 1), (2, 2)

DECLARE @C TABLE (act tinyint)

INSERT @C (act)
SELECT
m.iud
FROM
(
MERGE @T AS t
USING @S AS s ON s.id = t.id
WHEN NOT MATCHED THEN
INSERT VALUES (s.id, s.val)
WHEN MATCHED AND t.val <> s.val THEN
UPDATE SET t.val = s.val
WHEN MATCHED THEN DELETE
OUTPUT
CASE
WHEN $action = N'UPDATE' THEN CONVERT(TINYINT, 1)
WHEN $action = N'DELETE' THEN CONVERT(TINYINT, 3)
WHEN $action = N'INSERT' THEN CONVERT(TINYINT, 4)
END
AS iud
) AS m;

SELECT
act =
CASE c.act
WHEN 1 THEN 'Update'
WHEN 3 THEN 'Delete'
WHEN 4 THEN 'Insert'
END,
cnt = COUNT_BIG(*)
FROM @C AS c
GROUP BY
c.act





Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
sqlfriends
sqlfriends
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2045 Visits: 3857
Thank you a lot.

It seems Merge statement is a very helpful tool for data update/delete/insert load.

But at the same time, I understand it is not so flexible to add other codes into it. This one is an example.

You code is certainly a great help, I will give it a try.


Thanks
Paul White
Paul White
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10338 Visits: 11350
sqlfriends (1/9/2012)
It seems Merge statement is a very helpful tool for data update/delete/insert load.
But at the same time, I understand it is not so flexible to add other codes into it. This one is an example.

It would certainly be nice to be able to use GROUP BY $action and COUNT directly on the output, that's true.



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
sqlfriends
sqlfriends
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2045 Visits: 3857
Paul, while I am trying above SQL, I got an error:
Msg 102, Level 15, State 1, Line 13
Incorrect syntax near '@T'.

Cannot figure out why?

Thanks
Paul White
Paul White
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10338 Visits: 11350
sqlfriends (1/9/2012)
Incorrect syntax near '@T'.

Make sure you're running SQL Server 2008 and that the database you are connected to is set to compatibility level 100 (SQL Server 2008). Anything lower than that (e.g. 2005 compatibility level 90) and the MERGE statement causes the error. MERGE was new for 2008.



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
sqlfriends
sqlfriends
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2045 Visits: 3857
Thanks , that works perfectly!
chris.stuart
chris.stuart
SSC Veteran
SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)

Group: General Forum Members
Points: 214 Visits: 776
Currently I'm loading historical data and have no need for row counts and I've noticed a major speed increase when not using the output clause. As mentioned above, I wish there was just a group by function and count of the output clause.
Jason-299789
Jason-299789
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1134 Visits: 3229
Paul,

Is there a reason you dont use the native OUTPUT INTO @C?

_________________________________________________________________________
SSC Guide to Posting and Best Practices
Paul White
Paul White
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10338 Visits: 11350
Jason-299789 (12/12/2012)
Is there a reason you dont use the native OUTPUT INTO @C?

It was almost a year ago, but from the looks of things I just wanted to show composable DML working. It does offer an opportunity to do more interesting things in the outer INSERT, but you certainly could use a straight OUTPUT INTO in the code presented previously:


DECLARE @S TABLE (id integer PRIMARY KEY, val integer)
DECLARE @T TABLE (id integer PRIMARY KEY, val integer)

INSERT @S VALUES (1, 5), (2, 8), (3, 1), (4, 5), (5,9)
INSERT @T VALUES (1, 1), (2, 2)

DECLARE @C TABLE (act tinyint)

MERGE @T AS t
USING @S AS s ON s.id = t.id
WHEN NOT MATCHED THEN
INSERT VALUES (s.id, s.val)
WHEN MATCHED AND t.val <> s.val THEN
UPDATE SET t.val = s.val
WHEN MATCHED THEN DELETE
OUTPUT
CASE
WHEN $action = N'UPDATE' THEN CONVERT(TINYINT, 1)
WHEN $action = N'DELETE' THEN CONVERT(TINYINT, 3)
WHEN $action = N'INSERT' THEN CONVERT(TINYINT, 4)
END INTO @C;

SELECT
act =
CASE c.act
WHEN 1 THEN 'Update'
WHEN 3 THEN 'Delete'
WHEN 4 THEN 'Insert'
END,
cnt = COUNT_BIG(*)
FROM @C AS c
GROUP BY
c.act;





Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search