FONTE: (SQLServerCentral) http://www.sqlservercentral.com/columnists/jSebastian/3122_printver

Introduction

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=20070101CustomerNumber=J0001

OrderDate=2007-07-08/>

3 <ItemInfo>

4 <Item ItemNumber=A001Qty=10Rate=100/>

5 <Item ItemNumber=A002Qty=11Rate=200/>

6 <Item ItemNumber=A003Qty=12Rate=300/>

7 <Item ItemNumber=A004Qty=13Rate=400/>

8 <Item ItemNumber=A005Qty=14Rate=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.

Anúncios