MERGE – An UPSERT Example

I recently had to do an UPSERT. (Yes, it’s a real word: UPdate + inSERT.) This would normally be an UPDATE followed by in INSERT wrapped in a transaction so if either fails it all rolls back. This means you will make at least two passes through the data, locking it until the transaction commits. Logically this is a single operation, wouldn’t it be nice to have a single construct that could handle the UPDATE, the INSERT, and maybe even a DELETE if necessary? That’s exactly what we have in the MERGE statement. SQL Books Online gives a good explanation of the syntax and a few examples but it can get very complex very quickly.

USE tempdb
GO
    IF (OBJECT_ID('dbo.#Target')) IS NOT NULL 
        DROP TABLE #Target; 
        
    IF (OBJECT_ID('dbo.#Source')) IS NOT NULL 
        DROP TABLE #Source;        

    --Dupsert this table...
        CREATE TABLE #Target 
            (
                TargetID    INTEGER    IDENTITY PRIMARY KEY NOT NULL
                ,DataCol    CHAR(10)    NOT NULL
            );
        INSERT INTO #Target (DataCol) 
            VALUES ('Static'),('Update Me')
            ,('Delete Me')
            ;
        
    --...with changes from this one.
        CREATE TABLE #Source 
            (
                SourceID    INTEGER    IDENTITY(101,1) PRIMARY KEY NOT NULL
                ,TargetID    INTEGER    NULL 
                ,DataCol    CHAR(10)    NOT NULL
            );
        INSERT INTO #Source (DataCol,TargetID) 
            VALUES ('Static',1),('Change',2),('New',NULL);
        
    --View tables    
        SELECT * FROM #Target;
        SELECT * FROM #Source;

    --Dupsert the target using MERGE
        MERGE 
            INTO #Target 
            USING #Source
                ON    #Target.TargetID = #Source.TargetID
            WHEN MATCHED THEN UPDATE 
                SET #Target.DataCol = #Source.DataCol
            WHEN NOT MATCHED BY TARGET THEN 
                INSERT (DataCol)
                VALUES (#Source.DataCol)
            WHEN NOT MATCHED BY SOURCE THEN 
                DELETE;

    --View target    
        SELECT * FROM #Target;

As you can see, the code is a logical unit, straightforward, and flexible.  Enjoy!

T-SQL Tuesday #016 – Getting Totals And Subtotals With ROLLUP And CUBE

 

olap_1Welcome to TSQL Tuesday!  Click the picture on the right for more information.

TSQL is a great tool for aggregating lots of data but what do you do with it once it’s aggregated?  Often the answer is to aggregate it again through subtotals and totals.  Normally this will require a second query or if you want to see the totals “in-line”, a trip to Excel.  But, when using aggregation, you can use the WITH ROLLUP or WITH CUBE operator in the GROUP BY clause to generate totals.

MSDN explains the difference between ROLLUP and CUBE as follows:

  • CUBE generates a result set that shows aggregates for all combinations of values in the selected columns. 
  •  ROLLUP generates a result set that shows aggregates for a hierarchy of values in the selected columns.

 If that seems a little fuzzy don’t worry about it – just run the query below with each operator and note the difference in output. 

This query will run on SQL 2005 or 2008 DBs and shows the number of reads since the last restart.  Normally these operators (ROLLUP & CUBE) are used to present an application with pre-aggregated data for performance reasons.  However in this example it is quite useful for ad hoc I/O performance queries. 

SELECT

      OBJECT_SCHEMA_NAME(S.object_id)     AS SchemaName

      ,OBJECT_NAME(S.object_id)           AS ObjectName

      ,SUM(S.user_seeks + S.user_scans + S.user_lookups)                           AS TotalReads

FROM

      sys.dm_db_index_usage_stats         AS S

      INNER JOIN

      sys.indexes                         AS I

            ON S.object_id = I.object_id

WHERE

      OBJECTPROPERTY(S.object_id,‘IsUserTable’) = 1

      AND I.index_id = S.index_id

      AND S.database_id = DB_ID()

GROUP BY

      OBJECT_SCHEMA_NAME(S.object_id)

      ,OBJECT_NAME(S.object_id)

      –WITH ROLLUP / CUBE  

ORDER BY

      1,2,3

  In Figure1 we see the results from the basic aggregation.

 

 

 

 

Figure 1

Adding WITH ROLLUP produces the following.  Row 1 (NULL NULL) is a grand total.  Note that row 2 gives the sub-total for the Person group and so on.

 

 

 

 

 

Figure 2

Finally WITH CUBE produces all possible subtotals. 

 

 

 

 

 

 

 

 

Figure 3

 These operators aren’t Rocket Science but if they save you acouple minutes every time you’d have to make a trip to Excel then I think they are worth adding to the toolbox.