Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL Server 2008 - The Power of Merge

By Jacob Sebastian,

Introduction

In Part IV of my Sales Order Workshop, I had presented a stored procedure which saves a sales order information into Order Header and Order Detail tables. If you have ever worked on an order processing application, you would realize that saving a modified sales order is little tricky. There may be new rows added to the sales order. There may be rows which are updated and there may be rows that should be deleted. If you have the freedom to delete all the rows from order details table and re-insert everything, you are lucky. But many of the times you cannot simply delete the order details table because there may be additional information like Quantity-picked etc, which is updated from other parts of the application. In those scenarios, you need to perform a DELETE-UPDATE-INSERT operation to save the information correctly. The following is the pseudo code that I had used in my stored procedure.

    1 /*

    2     Pseudo code used for saving sales order information with SQL Server 2005

    3 

    4     -- save order header information

    5     If OrderNumber found in OrderHeader

    6         Update the information

    7     Else

    8         Insert the information

    9     end

   10 

   11     -- save order detail information

   12     Delete from Order detail table all items not in the order info

   13     Update Order detail for all items present in the order info

   14     Insert into order details all new items in the order info

   15 */

SQL Server 2008 introduces MERGE, a new keyword which performs INSERT, UPDATE and DELETE operations at one go. With SQL Server 2008, you can perform the above operation as simple as the following pseudo code.

    1 /*

    2     Pseudo code for saving the same order with the MERGE statement of SQL Server 2008

    3 

    4     -- save order header information

    5     MERGE order info to Order Header table

    6 

    7     -- save order detail information

    8     MERGE order info to order detail table

    9 */

No, I did not miss anything. You can write the code in just 2 lines. The rest of this article presents a SQL Server 2008 stored procedure which demonstrates this.

The Data

Here is the structure of the order data that we have. This XML has the order header and detail information. Our stored procedure needs to store the information in both tables. Some rows need to be updated, some inserted and some deleted.

    1 <OrderInfo>

    2   <OrderHeader OrderNumber="20070101" CustomerNumber="J0001" OrderDate="2007-07-08" />

    3   <ItemInfo>

    4     <Item ItemNumber="A001" Qty="10" Rate="100" />

    5     <Item ItemNumber="A002" Qty="11" Rate="200" />

    6     <Item ItemNumber="A003" Qty="12" Rate="300" />

    7     <Item ItemNumber="A004" Qty="13" Rate="400" />

    8     <Item ItemNumber="A005" Qty="14" Rate="500" />

    9   </ItemInfo>

   10 </OrderInfo>

Tables

We need two tables to store the order information. Here is the script to create the tables.

    1 CREATE TABLE [dbo].[OrderHeader](

    2     [OrderNumber] [varchar](20) NULL,

    3     [CustomerNumber] [varchar](20) NULL,

    4     [OrderDate] [datetime] NULL

    5 ) ON [PRIMARY]

    6 

    7 GO

    8 

    9 CREATE TABLE [dbo].[OrderDetails](

   10     [OrderNumber] [varchar](20) NULL,

   11     [ItemNumber] [varchar](20) NULL,

   12     [Qty] [int] NULL,

   13     [Rate] [money] NULL

   14 ) ON [PRIMARY]

   15 

   16 GO

Enter the Dragon

Let us see the Stored Procedure which uses the MERGE keyword.

    1 CREATE PROCEDURE [dbo].[MergeSalesOrder]

    2 (

    3     @OrderInfo XML

    4 )

    5 AS

    6 

    7 SET NOCOUNT ON

    8 

    9 /*

   10 I am not using a TRY..CATCH or BEGIN TRAN to simplify the code.

   11 */

   12 

   13 /*

   14 Code to save order header. I am creating a CTE over the XML data to simplify

   15 the code.

   16 */

   17 

   18 ;WITH OrderInfo AS (

   19     SELECT

   20         x.h.value('@OrderNumber', 'VARCHAR(20)') AS OrderNumber,

   21         x.h.value('@CustomerNumber', 'VARCHAR(20)') AS CustomerNumber,

   22         x.h.value('@OrderDate', 'VARCHAR(20)') AS OrderDate

   23     FROM @OrderInfo.nodes('/OrderInfo/OrderHeader') AS x(h)

   24 )

   25 MERGE OrderHeader AS h

   26 USING OrderInfo AS o

   27 ON (h.OrderNumber = o.OrderNumber)

   28 WHEN MATCHED THEN

   29     UPDATE SET h.CustomerNumber = o.CustomerNumber, h.OrderDate = o.OrderDate

   30 WHEN NOT MATCHED THEN

   31     INSERT (OrderNumber, CustomerNumber, OrderDate)

   32     VALUES (o.OrderNumber, o.CustomerNumber, o.OrderDate)

   33 ;

   34 

   35 /*

   36 Save Order Detail Information

   37 */

   38 

   39 ;WITH OrderInfo AS (

   40     SELECT

   41         x.h.value('(../../OrderHeader/@OrderNumber)[1]', 'VARCHAR(20)') AS OrderNumber,

   42         x.h.value('@ItemNumber', 'VARCHAR(20)') AS ItemNumber,

   43         x.h.value('@Qty', 'INT') AS Qty,

   44         x.h.value('@Rate', 'MONEY') AS Rate

   45     FROM @OrderInfo.nodes('/OrderInfo/ItemInfo/Item') AS x(h)

   46 )

   47 MERGE OrderDetails AS d

   48 USING OrderInfo AS o

   49 ON (d.OrderNumber = o.OrderNumber AND d.ItemNumber = o.ItemNumber)

   50 WHEN MATCHED THEN

   51     UPDATE SET

   52         d.ItemNumber = o.ItemNumber,

   53         d.Qty = o.Qty,

   54         d.Rate = o.Rate

   55 WHEN NOT MATCHED THEN

   56     INSERT (OrderNumber, ItemNumber, Qty, Rate)

   57     VALUES (o.OrderNumber, o.ItemNumber, o.Qty, o.Rate)

   58 WHEN SOURCE NOT MATCHED THEN

   59     DELETE

   60 ;

   61 

   62 /*

   63 Points to note:

   64 1. MERGE statement should be terminated with a semi colon

   65 2. The JOIN (USING...ON) should not result in duplicate records.

   66 3. When the records in the SOURCE and TARGET matches, MATCHED becomes true

   67 4. When the record is not in the TARGET, NOT MATCHED becomes true

   68 5. When the record is not in the SOURCE, SOURCE NOT MATCHED becomes true.

   69 */

Execute the code

It is time to execute the code. Use the following code to execute the stored procedure.

    1 EXECUTE [MergeSalesOrder] '

    2 <OrderInfo>

    3   <OrderHeader OrderNumber="20070101" CustomerNumber="J0001" OrderDate="2007-07-08" />

    4   <ItemInfo>

    5     <Item ItemNumber="A001" Qty="10" Rate="100" />

    6     <Item ItemNumber="A002" Qty="11" Rate="200" />

    7     <Item ItemNumber="A003" Qty="12" Rate="300" />

    8     <Item ItemNumber="A004" Qty="13" Rate="400" />

    9     <Item ItemNumber="A005" Qty="14" Rate="500" />

   10   </ItemInfo>

   11 </OrderInfo>

   12 '

   13 

   14 /*

   15 Let us check the results

   16 */

   17 SELECT * FROM OrderHeader

   18 SELECT * FROM OrderDetails

   19 

   20 /*

   21 OrderNumber         CustomerNumber       OrderDate

   22 -------------------- -------------------- -----------------------

   23 20070101             J0001                2007-07-08 00:00:00.000

   24 

   25 (1 row(s) affected)

   26 

   27 OrderNumber         ItemNumber           Qty         Rate

   28 -------------------- -------------------- ----------- ---------------------

   29 20070101             A001                 10         100.00

   30 20070101             A002                 11         200.00

   31 20070101             A003                 12         300.00

   32 20070101             A004                 13         400.00

   33 20070101             A005                 14         500.00

   34 

   35 (5 row(s) affected)

   36 */

The above code shows that the order is saved correctly. Now lets us modify the order info. Let us delete a row, add a new row and modify an existing row. Here is the code. Note that Item A005 is deleted. Item A006 is added and item A001 is modified. Let us execute the code and see the results.

    1 EXECUTE [MergeSalesOrder] '

    2 <OrderInfo>

    3   <OrderHeader OrderNumber="20070101" CustomerNumber="J0001" OrderDate="2007-07-08" />

    4   <ItemInfo>

    5     <Item ItemNumber="A001" Qty="15" Rate="150" />

    6     <Item ItemNumber="A002" Qty="11" Rate="200" />

    7     <Item ItemNumber="A003" Qty="12" Rate="300" />

    8     <Item ItemNumber="A004" Qty="13" Rate="400" />

    9     <Item ItemNumber="A006" Qty="16" Rate="600" />

   10   </ItemInfo>

   11 </OrderInfo>

   12 '

   13 

   14 SELECT * FROM OrderHeader

   15 SELECT * FROM OrderDetails

   16 

   17 /*

   18 OUTPUT:

   19 

   20 OrderNumber         CustomerNumber       OrderDate

   21 -------------------- -------------------- -----------------------

   22 20070101             J0001                2007-07-08 00:00:00.000

   23 

   24 (1 row(s) affected)

   25 

   26 OrderNumber         ItemNumber           Qty         Rate

   27 -------------------- -------------------- ----------- ---------------------

   28 20070101             A001                 15         150.00

   29 20070101             A002                 11         200.00

   30 20070101             A003                 12         300.00

   31 20070101             A004                 13         400.00

   32 20070101             A006                 16         600.00

   33 

   34 (5 row(s) affected)

   35 

   36 */

Conclusions

I found the MERGE keyword very powerful and friendly. It reduces the complexity of the code and provides a simple interface to perform a complex operation. I like it and I suppose many of you around there would like it too.

Total article views: 13596 | Views in the last 30 days: 15
 
Related Articles
ARTICLE

Sales Order Workshop Part III

In the previous articles, Jacob Sebastian looked at using XML to save a sales order with variable nu...

ARTICLE

Catching Deadlock Information in SQL Logs

This article presents a handy way to retrieve deadlock information in an ordered way from the error ...

ARTICLE

Saving a Sales Order Part 1

How many times have you tried to save a sales order to your database? For many DBAs this is a common...

BLOG

View SQL Server information using Transact-SQL script

Recently one of our clients requested information about their SQL Server infrastructure which we man...

FORUM

Information about reports ran

Information about reports ran

Tags
advanced querying    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones