Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

merge statement - to get a row count Expand / Collapse
Author
Message
Posted Monday, January 9, 2012 11:24 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 9:42 AM
Points: 1,749, Visits: 3,155
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
Post #1232689
Posted Monday, January 9, 2012 11:51 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:58 AM
Points: 11,192, Visits: 11,097
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1232709
Posted Monday, January 9, 2012 12:02 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 9:42 AM
Points: 1,749, Visits: 3,155
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
Post #1232719
Posted Monday, January 9, 2012 12:14 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:58 AM
Points: 11,192, Visits: 11,097
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1232726
Posted Monday, January 9, 2012 12:26 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 9:42 AM
Points: 1,749, Visits: 3,155
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
Post #1232731
Posted Monday, January 9, 2012 12:30 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:58 AM
Points: 11,192, Visits: 11,097
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1232738
Posted Monday, January 9, 2012 12:33 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 9:42 AM
Points: 1,749, Visits: 3,155
Thanks , that works perfectly!
Post #1232740
Posted Wednesday, December 12, 2012 12:49 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, July 17, 2014 3:04 AM
Points: 201, Visits: 676
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.
Post #1395473
Posted Wednesday, December 12, 2012 2:16 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, July 18, 2014 9:09 AM
Points: 870, Visits: 2,385
Paul,

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


_________________________________________________________________________
SSC Guide to Posting and Best Practices
Post #1395516
Posted Wednesday, December 12, 2012 9:11 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:58 AM
Points: 11,192, Visits: 11,097
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1395744
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse