Pastebin launched a little side project called HostCabi.net, check it out ;-)Don't like ads? PRO users don't see any ads ;-)
Guest

Not possible to capture rowcounts when using Composable DML

By: jamiet on Feb 7th, 2013  |  syntax: T-SQL  |  size: 1.82 KB  |  hits: 78  |  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 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