January 9, 2012 at 11:24 am
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
January 9, 2012 at 11:51 am
There's no direct way (that I know of), but:
DECLARE @s-2 TABLE (id integer PRIMARY KEY, val integer)
DECLARE @T TABLE (id integer PRIMARY KEY, val integer)
INSERT @s-2 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-2 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
SQLkiwi blog
@SQL_Kiwi
January 9, 2012 at 12:02 pm
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
January 9, 2012 at 12:14 pm
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
SQLkiwi blog
@SQL_Kiwi
January 9, 2012 at 12:26 pm
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
January 9, 2012 at 12:30 pm
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
SQLkiwi blog
@SQL_Kiwi
January 9, 2012 at 12:33 pm
Thanks , that works perfectly!
December 12, 2012 at 12:49 am
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.
December 12, 2012 at 2:16 am
Paul,
Is there a reason you dont use the native OUTPUT INTO @C?
_________________________________________________________________________
SSC Guide to Posting and Best Practices
December 12, 2012 at 9:11 am
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-2 TABLE (id integer PRIMARY KEY, val integer)
DECLARE @T TABLE (id integer PRIMARY KEY, val integer)
INSERT @s-2 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-2 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
SQLkiwi blog
@SQL_Kiwi
December 12, 2012 at 11:57 pm
Thanks Paul,
It just seemed a little overkill for this scenario, but I have seen a lot of 'funky' stuff done with the outer insert on a merge though it can take a little time to get your head round what trhe code is trying to do.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply