Advertisement
jamiet

Not possible to capture rowcounts when using Composable DML

Feb 7th, 2013
188
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
T-SQL 1.82 KB | None | 0 0
  1. /******************************************************************************************************************************
  2. A demonstration of capturing rowcounts when using composable DML. The problem I'm trying to demonstrate here is that
  3. I don't think there is a way to capture the number of rows affected by the MERGE
  4.  
  5. Jamie Thomson, 2013-02-07
  6. ******************************************************************************************************************************/
  7.  
  8. /*Setup table first and insert some data into [src]*/
  9. USE tempdb
  10. IF OBJECT_ID('src') IS NOT NULL     DROP TABLE src;
  11. CREATE TABLE src (
  12.     id      INT
  13. ,   name    NVARCHAR(MAX)
  14. );
  15. IF OBJECT_ID('tgt') IS NOT NULL     DROP TABLE tgt;
  16. CREATE TABLE tgt (
  17.     id      INT
  18. ,   name    NVARCHAR(MAX)
  19. );
  20. /*[updates] will be used as the target of the Composable DML insertion*/
  21. IF OBJECT_ID('old_values') IS NOT NULL      DROP TABLE old_values;
  22. CREATE TABLE old_values (
  23.     id      INT
  24. ,   name    NVARCHAR(MAX)
  25. );
  26. INSERT src(id,name)VALUES(1,'don'),(2,'kaina');
  27. GO
  28.  
  29. /*Everything after here gets run twice because the batch ends with GO 2*/
  30. INSERT old_values( id, name )
  31. SELECT  mrgout.deleted_id
  32. ,       mrgout.deleted_name
  33. FROM    (
  34.         MERGE   tgt
  35.         USING   src
  36.             ON  tgt.id = src.id
  37.         WHEN MATCHED THEN
  38.             UPDATE 
  39.             SET tgt.NAME = src.NAME
  40.           WHEN NOT MATCHED THEN  
  41.             INSERT(id,name)
  42.             VALUES(src.id,src.name)
  43.         OUTPUT $ACTION AS action_
  44.         ,       INSERTED.id AS inserted_id
  45.         ,       INSERTED.NAME AS inserted_name
  46.         ,       DELETED.id AS deleted_id
  47.         ,       DELETED.name AS deleted_name
  48.         )mrgout
  49. WHERE   mrgout.action_ = 'UPDATE' --Filtering on $action=UPDATE allows us to get the replaced values from DELETED virtual table
  50. ;
  51. SELECT  [@@ROWCOUNT]=@@ROWCOUNT,row_tally_in_tgt=(SELECT COUNT(*) FROM tgt)  -- <-Rowcount only provides tally of rows affected by the outer INSERT, not the MERGE
  52. GO 2
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement