What’s New in T-SQL - Pt. 1

Continuing, or should I say, beginning, our look at SQL Server 2008, I will be spending the next few posts discussing new and improved features in T-SQL.
Let’s start by examining the MERGE statement.
What does the MERGE statement let you do? The MERGE statement will allow you to Insert, Update, and Delete rows in your target table in just a single statement. Before the MERGE statement you would have to do each operation separately using complex join statements or WHERE clauses.      

Syntax:

(from SQL 2008 Books Online)

[ WITH <common_table_expression> [,...n] ]
MERGE 
[ TOP ( expression ) [ PERCENT ] ] 
[ INTO ] target_table [ [ AS ] table_alias ]
        [ WITH ( <merge_hint> ) ]
USING <table_source>
ON <search_condition>
[ WHEN MATCHED [ AND <search_condition> ]
        THEN <merge_matched> ]
[ WHEN [TARGET] NOT MATCHED [ AND <search_condition> ]
        THEN <merge_not_matched> ]
[ WHEN SOURCE NOT MATCHED [ AND <search_condition> ]
        THEN <merge_ matched> ]
<output_clause>
[ OPTION ( <query_hint> [ ,...n ] ) ];

<merge_hint>::=
        { [ <table_hint_limited> [ ,...n ] ]
    [ [ , ] INDEX ( index_val [ ,...n ] ) ] }

<table_source> ::= 
{
        table_or_view_name [ [ AS ] table_alias ] [ <tablesample_clause> ] 
                [ WITH ( table_hint [ [ , ]...n ] ) ] 
    | rowset_function [ [ AS ] table_alias ] 
                [ ( bulk_column_alias [ ,...n ] ) ] 
    | user_defined_function [ [ AS ] table_alias ]
    | OPENXML <openxml_clause> 
    | derived_table [ AS ] table_alias [ ( column_alias [ ,...n ] ) ] 
    | <joined_table> 
    | <pivoted_table> 
    | <unpivoted_table> 
}

<merge_matched>::=
                { UPDATE SET <set_clause> | DELETE }

<set_clause>::=
    { column_name = { expression | DEFAULT | NULL }
  | { udt_column_name. { { property_name = expression 
        | field_name = expression } 
        | method_name ( argument [ ,...n ] ) } }
  | column_name { .WRITE ( expression , @Offset , @Length ) }
  | @variable = expression 
    } [ ,...n ] 

<merge_not_matched>::=
        INSERT [ ( <column_list> ) ] 
                { VALUES ( <values_list> )
             | DEFAULT VALUES }

<output_clause>::=
{
    [ OUTPUT <dml_select_list> INTO { @table_variable | output_table }
        [ (column_list) ] ]
        [ OUTPUT <dml_select_list> ]
}

<dml_select_list>::=
{ <column_name> | scalar_expression } [ [AS] column_alias_identifier ]
     [ ,...n ]

<column_name> ::=
    { DELETED | INSERTED | from_table_name } . { * | column_name }
   | $ACTION

Example:

Scenario: You regularly update a table from a bulk source. The incoming dataset has new records and updates to existing records.
You maintain a table of the current inventory, which is updated nightly to reflect the transactions from the day.

Assumptions: Products are kept in a Product table which is not shown below. A foreign-key relationship exists with the Inventory table. The LastModified field is automatically populated by a default for all inserts.

DECLARE @InventoryMaster TABLE(InventoryID INT IDENTITY,
ProductID INT,
QtyOnHand INT,
ReorderLevel INT,
LastModified datetime DEFAULT GETDATE()
);

DECLARE @DailyTransactions TABLE(ProductID INT, TransQty INT);

INSERT INTO @InventoryMaster (ProductID, QtyOnHand, ReorderLevel) 
VALUES (1, 100, 20), (2, 50, 10), (3, 1000, 100), (25, 200, 50), (26, 200, 20);

INSERT INTO @DailyTransactions (ProductID, TransQty)
VALUES (1, -20), (1, 5), (3, -25), (3, 10), (25, -5), (24, 10), (25, 50);

SELECT * FROM @InventoryMaster;

MERGE @InventoryMaster inv
USING (SELECT ProductID, SUM(TransQty) as TransQty FROM @DailyTransactions GROUP BY ProductID) dt
ON inv.ProductID = dt.ProductID
WHEN MATCHED
THEN UPDATE SET QtyOnHand += TransQty
WHEN NOT MATCHED 
THEN INSERT VALUES(ProductID, TransQty, TransQty*.25,GetDate())
OUTPUT $action,inserted.*,deleted.*;

SELECT * FROM @InventoryMaster;

 

You will notice that the insert statements are a little different than you may be used to. The above example utilizes row constructors (new to SQL Server 2008). You will also keenly observe the use of "+=", and realize that SQL Server 2008 must now allow compound assignment operators, and you would be right. We will cover both of these in tomorrows post.

 

For now, be aware that there are many ways to construct your MERGE statement. The example utilizes a sub-select query from the daily transaction table to sum the amount deltas together. If they aren't summed you will get an error because you are not allowed to perform multiple updates on a row within the same MERGE statement.

Katmai 1st Look

A few weeks ago I presented at a Microsoft/Sogeti USA joint event in Dayton. The topic was SQL Server 2008 - What’s New. I had just over an hour for my presentation, and it turned out this was just not nearly enough time to do justice to all the new and improved features found in Katmai. I have been looking for something to motivate me into starting a blog and now it looks like I have found something. In order to rectify the injustice done to the topic because of time constraints I will be publishing a series of posts on my blog. In each post we’ll explore a new feature or an improvement in depth. Join with me and participate with your comments. Together we will uncover the mysteries of SQL Server 2008.

Look for my next post to begin our journey with "What’s New in T-SQL"