35

I'm wondering what y'alls would recommend as the best way to go about getting the action counts from a MERGE statement in Sql Server.

So, i.e. I run a MERGE which does some inserts, some updates and some deletes, ... I would like to be able to find out HOW MANY inserts, HOW MANY updates and How Many deletes.

What'd be the best way to do this?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
eidylon
  • 7,068
  • 20
  • 75
  • 118

3 Answers3

39

You could specify an OUTPUT clause on your MERGE statement and get an output report of what's been done during MERGE. 

MERGE (targetTable) AS t 
USING (sourceTable) AS s
ON t.ID = s.ID
WHEN MATCHED THEN
  (some statements)
WHEN NOT MATCHED THEN
  (some statements)
OUTPUT
  $action, inserted.ID 'inserted', deleted.ID 'deleted'
;

This will give you a row for each "action" (insert, update, delete) for each operation. If it's a lot of statements, you could also OUTPUT INTO @tableVar and then look at the table variable.

DECLARE @tableVar TABLE (MergeAction VARCHAR(20), InsertedID INT, DeletedID INT)

MERGE (targetTable) AS t 
USING (sourceTable) AS s
ON t.ID = s.ID
WHEN MATCHED THEN
      (some statements)
WHEN NOT MATCHED THEN
      (some statements)
OUTPUT
      $action, inserted.ID 'inserted', deleted.ID 'deleted' INTO @tableVar
;

SELECT MergeAction, COUNT(*) 
FROM @tableVar  
GROUP BY MergeAction

Check out the Books Online for details on the MERGE statement and the OUTPUT clause.

Marc

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • Is it possible somehow to do this in one statement, possibly with a group by? so that the output bit would be something sort of along the lines of > OUTPUT $ACTION, COUNT(1) GROUP BY $ACTION ? – eidylon Aug 12 '09 at 20:43
  • No, you cannot do this. You can either just OUTPUT to the SSMS output window, or into a table variable - don't over-stretch the OUTPUT clause!! :-) – marc_s Aug 12 '09 at 20:49
  • Ah well... Actually, does OUTPUT put out a table variable, or a resultset? Would it be possible to wrap the MERGE statement with an OUTPUT clause inside a SELECT statement as the source (as a subquery) and then have that outer SELECT do the aggregation? ... Sounds possible. I may have to play with that. – eidylon Aug 12 '09 at 20:58
  • OUTPUT can send its output directly to your result grid in SSMS, or into a table variable - that's it, according to the MSDN library. You cannot do any of the trickery you want to do - you'll have to use the steps as I showed them - sorry, you're trying to do something that SQL Server does not support – marc_s Aug 12 '09 at 21:05
  • You cannot seem to use the MERGE statement in either a SELECT * FROM (MERGE as a subselect) or a Common Table Expression (WITH MergeOutput AS (MERGE:......)SELECT MergeAction.....) - it just doesn't work that way. You can output your values into the output window, a table variable, or a new table (on disk) - but you cannot use it directly as an output result set. – marc_s Aug 12 '09 at 21:11
  • I messed around, with this solution and got it working for my needs. Thanks! – eidylon Aug 14 '09 at 15:20
  • marc_S: what about the case you want to merge million rows? Is it that table variable the most efficient way to perform the count? – bjnr Sep 02 '14 at 08:39
  • @MihaiBejenariu: table variables are **highly inefficient** for large amounts of data - the query optimizer always assumes they contain **one row of data** only, and thus query execution can go awfully wrong due to a highly inefficient execution plan. Use a proper temp table instead in such a scenario – marc_s Sep 02 '14 at 08:40
  • @marc_s: just testing a scenario with 3M rows: it takes 18s to perform the merge, 36 to perform the merge + count from temp table, 39s to perform the merge + count from table variable. It seems it doubles the time in order to perform the count. – bjnr Sep 02 '14 at 08:48
  • @MihaiBejenariu - add a clustered index to your table variable and it will speed up the counting. – Ross Presser Dec 02 '19 at 21:01
5

To extract into individual vars, can post process answer by marc_s using pivot:

    declare
        @mergeResultsTable table (MergeAction VARCHAR(20));

    declare
        @insertCount int,
        @updateCount int,
        @deleteCount int;

    merge ...
    output $action into @mergeResultsTable; 

    select @insertCount = [INSERT],
           @updateCount = [UPDATE],
           @deleteCount = [DELETE]
      from (select 'NOOP' MergeAction -- row for null merge into null
             union all
            select * from @mergeResultsTable) mergeResultsPlusEmptyRow     
     pivot (count(MergeAction) 
       for MergeAction in ([INSERT],[UPDATE],[DELETE])) 
        as mergeResultsPivot;

The union 'noop' row can be removed if init vars to 0 or know that source or target table has >0 rows.

crokusek
  • 5,345
  • 3
  • 43
  • 61
2

How about:

INSERT YourResultsTable (action, cnt)
SELECT action, count(*)
FROM
(
    MERGE (targetTable) AS t 
    USING (sourceTable) AS s
       ON t.ID = s.ID
    WHEN MATCHED THEN      (some statements)
    WHEN NOT MATCHED THEN      (some statements)
    OUTPUT $action as action, inserted.ID as ins, deleted.ID as del
) m
GROUP BY action;

[Edit] Ok, so try:

INSERT YourResultsTable (action)
SELECT action
FROM
(
    MERGE (targetTable) AS t 
    USING (sourceTable) AS s
       ON t.ID = s.ID
    WHEN MATCHED THEN      (some statements)
    WHEN NOT MATCHED THEN      (some statements)
    OUTPUT $action as action, inserted.ID as ins, deleted.ID as del
) m;

(and then count the results)

Rob

Rob Farley
  • 15,625
  • 5
  • 44
  • 58
  • Nope: Msg 10717, Level 15, State 1, Line 48 The GROUP BY clause is not allowed when the FROM clause contains a nested INSERT, UPDATE, DELETE, or MERGE statement. – marc_s Aug 13 '09 at 08:15
  • Tested on SQL Server 2008 Developer Edition – marc_s Aug 13 '09 at 08:15
  • I used your edit, then took that table you inserted into and aggregated the individual actions into SUM-Totals using Case-Statements and logged those results. It works great! It stinks that SQL Server forces me to Insert the merge-results into a table-variable instead of aggregating and setting my int-variables directly in the outermost Select. – MikeTeeVee Apr 08 '14 at 23:52