There has been significant T-SQL improvements in SQL Server 2008. This post explores the most notable changes in T-SQL introduced in SQL Server 2008.
The MERGE Statement
As name says, It combines Insert,Update, and Delete operations along with the Select operation which provides the source and target data for merge. In earlier versions, We need to write separate multiple statements to achieve the same.
Advantage: Simple code easier to write and maintain than writing code using separate statements. The MERGE Statement is particularly suited to Data Warehousing scenarios.
Let us take Stock and Trades tables as an example to demonstrate the MERGE statement feature. Create these two tables where Stock table contains the shares we own and Trades table contain share we make.
Insert some shares in Stock Table
Assume we bought 5 New shares for MSFT and sold 5 shares for WCP and bought 3 new NWT shares for trade.
These will be stored in Trade table as follows
The Contents of the two table are
We need to update the Quantities in Stock table to reflect the changes in Trade table. In Earlier versions we could have written a Join statement to detect the changes in Stock Table as result of change in the Trades table and perform Insert,Update and Delete operations to apply those changes to Stock Table.
All the above logic can now be performed with a single statement using MERGE statement.
The Statement begins with MERGE keyword, followed by USING and ON Keywords. The first table name after MERGE keyword is Target Table name and Second table after USING key word is Source table for MERGE operation.
NOTE: The semicolon at the end of query is mandatory and you will receive an error if you omit it.
Reference: Programming Microsoft SQL Server 2008 book
|Share this post :|