Sql Server 2008 MERGE - best way to get counts

Question

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?

Solution 1

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

Solution 2

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.

Solution 3

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

Comments

[eidylon]

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 ?

[marc_s]

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!! :-)

[eidylon]

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.

[marc_s]

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]

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]

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]

Tested on SQL Server 2008 Developer Edition

[eidylon]

I messed around, with this solution and got it working for my needs. Thanks!

[MikeTeeVee]

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.

[bjnr]

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?

[marc_s]

@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

[bjnr]

@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.

[Ross Presser]

@MihaiBejenariu - add a clustered index to your table variable and it will speed up the counting.