Advertisement
jamiet

Capturing rowcounts when using Composable DML

Feb 7th, 2013
138
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
T-SQL 2.21 KB | None | 0 0
  1. /******************************************************************************************************************************
  2. A solution to the problem outlined at http://pastebin.com/5wBsTVx9, that rowcoutns cannot be captured when using Composable DML
  3.  
  4. Jamie Thomson, 2013-02-07
  5. ******************************************************************************************************************************/
  6.  
  7. /*Setup table first and insert some data into [src]*/
  8. USE tempdb
  9. IF OBJECT_ID('src') IS NOT NULL     DROP TABLE src;
  10. CREATE TABLE src (
  11.     id      INT
  12. ,   name    NVARCHAR(MAX)
  13. );
  14. IF OBJECT_ID('tgt') IS NOT NULL     DROP TABLE tgt;
  15. CREATE TABLE tgt (
  16.     id      INT
  17. ,   name    NVARCHAR(MAX)
  18. );
  19. /*[updates] will be used as the target of the Composable DML insertion*/
  20. IF OBJECT_ID('old_values') IS NOT NULL      DROP TABLE old_values;
  21. CREATE TABLE old_values (
  22.     id      INT
  23. ,   name    NVARCHAR(MAX)
  24. );
  25. IF OBJECT_ID('mrgout') IS NOT NULL      DROP TABLE mrgout;
  26. CREATE TABLE mrgout (
  27.     action_         NVARCHAR(MAX)
  28. ,   inserted_id     INT
  29. ,   inserted_name    NVARCHAR(MAX)
  30. ,   deleted_id      INT
  31. ,   deleted_name    NVARCHAR(MAX)
  32. );
  33. INSERT src(id,name)VALUES(1,'don'),(2,'kaina');
  34. GO
  35.  
  36. /*Everything after here gets run twice because the batch ends with GO 2*/
  37. TRUNCATE TABLE mrgout;
  38. INSERT mrgout(action_, inserted_id, inserted_name,deleted_id, deleted_name )
  39. SELECT  mrgout.action_
  40. ,       mrgout.inserted_id
  41. ,       mrgout.inserted_name
  42. ,       mrgout.deleted_id
  43. ,       mrgout.deleted_name
  44. FROM    (
  45.         MERGE   tgt
  46.         USING   src
  47.             ON  tgt.id = src.id
  48.         WHEN MATCHED THEN
  49.             UPDATE 
  50.             SET tgt.NAME = src.NAME
  51.           WHEN NOT MATCHED THEN  
  52.             INSERT(id,name)
  53.             VALUES(src.id,src.name)
  54.         OUTPUT $ACTION AS action_
  55.         ,       INSERTED.id AS inserted_id
  56.         ,       INSERTED.NAME AS inserted_name
  57.         ,       DELETED.id AS deleted_id
  58.         ,       DELETED.name AS deleted_name
  59.         )mrgout
  60. ;
  61. INSERT  dbo.old_values(id,name)
  62. SELECT  deleted_id,deleted_name FROM mrgout
  63. WHERE   mrgout.action_ = 'UPDATE' --Filtering on $action=UPDATE allows us to get the replaced values from DELETED virtual table
  64. SELECT  [INSERT_@@ROWCOUNT]=(SELECT COUNT(*) FROM mrgout WHERE action_ = 'INSERT'),[UPDATE_@@ROWCOUNT]=(SELECT COUNT(*) FROM mrgout WHERE action_ = 'UPDATE'),row_tally_in_tgt=(SELECT COUNT(*) FROM tgt)  
  65. GO 2
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement