daily pastebin goal
58%
SHARE
TWEET

Untitled

a guest May 17th, 2018 94 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. MERGE (targetTable) AS t
  2. USING (sourceTable) AS s
  3. ON t.ID = s.ID
  4. WHEN MATCHED THEN
  5.   (some statements)
  6. WHEN NOT MATCHED THEN
  7.   (some statements)
  8. OUTPUT
  9.   $action, inserted.ID 'inserted', deleted.ID 'deleted'
  10. ;
  11.    
  12. DECLARE @tableVar TABLE (MergeAction VARCHAR(20), InsertedID INT, DeletedID INT)
  13.  
  14. MERGE (targetTable) AS t
  15. USING (sourceTable) AS s
  16. ON t.ID = s.ID
  17. WHEN MATCHED THEN
  18.       (some statements)
  19. WHEN NOT MATCHED THEN
  20.       (some statements)
  21. OUTPUT
  22.       $action, inserted.ID 'inserted', deleted.ID 'deleted' INTO @tableVar
  23. ;
  24.  
  25. SELECT MergeAction, COUNT(*)
  26. FROM @tableVar  
  27. GROUP BY MergeAction
  28.    
  29. declare
  30.         @mergeResultsTable table (MergeAction VARCHAR(20));
  31.  
  32.     declare
  33.         @insertCount int,
  34.         @updateCount int,
  35.         @deleteCount int;
  36.  
  37.     merge ...
  38.     output $action into @mergeResultsTable;
  39.  
  40.     select @insertCount = [INSERT],
  41.            @updateCount = [UPDATE],
  42.            @deleteCount = [DELETE]
  43.       from (select 'NOOP' MergeAction -- row for null merge into null
  44.              union all
  45.             select * from @mergeResultsTable) mergeResultsPlusEmptyRow    
  46.      pivot (count(MergeAction)
  47.        for MergeAction in ([INSERT],[UPDATE],[DELETE]))
  48.         as mergeResultsPivot;
  49.    
  50. INSERT YourResultsTable (action, cnt)
  51. SELECT action, count(*)
  52. FROM
  53. (
  54.     MERGE (targetTable) AS t
  55.     USING (sourceTable) AS s
  56.        ON t.ID = s.ID
  57.     WHEN MATCHED THEN      (some statements)
  58.     WHEN NOT MATCHED THEN      (some statements)
  59.     OUTPUT $action as action, inserted.ID as ins, deleted.ID as del
  60. ) m
  61. GROUP BY action;
  62.    
  63. INSERT YourResultsTable (action)
  64. SELECT action
  65. FROM
  66. (
  67.     MERGE (targetTable) AS t
  68.     USING (sourceTable) AS s
  69.        ON t.ID = s.ID
  70.     WHEN MATCHED THEN      (some statements)
  71.     WHEN NOT MATCHED THEN      (some statements)
  72.     OUTPUT $action as action, inserted.ID as ins, deleted.ID as del
  73. ) m;
RAW Paste Data
Top