This week only. Pastebin PRO Accounts Christmas Special! Don't miss out!Want more features on Pastebin? Sign Up, it's FREE!
Guest

Capturing rowcounts when using Composable DML

By: jamiet on Feb 7th, 2013  |  syntax: T-SQL  |  size: 2.21 KB  |  views: 49  |  expires: Never
download  |  raw  |  embed  |  report abuse  |  print
Text below is selected. Please press Ctrl+C to copy to your clipboard. (⌘+C on Mac)
  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
clone this paste RAW Paste Data