SQL Server 2008 New Features - Merge Statement
 
Published: 22 Aug 2008
Abstract
In this article Nidal examines the usage of a SQL Server 2008 feature named Merge statement. It is a new TSQL Statement that allows you to perform join of tables and then apply one of three different operations on the result set. He demonstrates the concepts with the help of step-by-step explanations accompanied by relevant SQL statements.
by Nidal Arabi
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 19789/ 79

Introduction

Microsoft SQL Server 2008 with its release included some new features that some of us (as developers) will find interesting and useful. In this article about the new features of SQL Server 2008, I will try to shed some light on the Merge Statement feature (a nifty addition) as attributed to the family of T-SQL.

About The Feature

Before the merge statement was introduced, a developer was required to do multiple statements to correct database status including creation of temporary table as well as writing successive queries. I will show in this article how to use the merge statement to enhance database integrity for example.

Requirements

In order to apply the article procedures, you should have installed any version of Microsoft SQL Server 2008 (Express, Standard, Developer, or Enterprise) and applied the first article of this series - SQL Server 2008 New Features - Row Constructors.

Step-by-Step Explanation

Follow the easy steps below to create your environment and work area.

1.    Open Microsoft SQL Server Management Studio.

2.    Right click the database DbWork and choose query window.

3.    Use the listing below to create an order table that would contain all the orders by a specific customer id.

Listing 1 - T-SQL to create the order table

CREATE TABLE [dbo].[TblOrder](
      [OrderId] [bigintIDENTITY(1,1) NOT NULL,
      [CustomerId] [intNOT NULL,
      [OrderTotal] [decimal](18, 0) NOT NULLON [PRIMARY]

4.    Use the following listing in order to insert some records in the order tables.

Listing 2 - T-SQL to insert data into the order table using the row constructor method

Insert into TblOrder (CustomerId, OrderTotal)
Values      (1,1000),
            (2,1500),
            (6,10000)

5.    We now need to add a column to the customer table and initialize the value to zero. Use the listing below.

Listing 3 - T-SQL to alter the customer table to add the customer total column

Alter Table TblCustomer
Add CustomerTotal Decimal Default 0;
  
Update TblCustomer
Set CustomerTotal = 0;

6.    In the listing of step 4 above you can see that I did make two orders for an existing two customers and added one order for a customer id 10 (does not exist in the customer table).

7.    Suppose your manager came in and said we have discrepancies in our database. We would like to have for each customer id to have customer total and every customer id found in the order table should have dummy customer information in order to have an integral database.

8.    How to solve the problem? Well SQL Server 2008 provided the merge statement to do this. The Merge statement allows us to Insert, Update or Delete on target table based on the results of a join with a source table (Wait for the example).

9.    Our target table here would be the customer table. Our source table would be the order table.

10. I will post the listing and then explain it.

Listing 4 - T-SQL Merge statement example

  Merge TblCustomer
  Using ( Select CustomerId, OrderTotal From TblOrder ) As OrderSrc (CustId, Amount)
  On TblCustomer.CustomerId = OrderSrc.CustId
  When Matched Then
      Update Set CustomerTotal = CustomerTotal + OrderSrc.Amount
  When Target Not Matched Then
      Insert (CustomerName, CustomerSince, CustomerArea, CustomerTotal)
      values ('New',Getdate(), 'New', OrderSrc.Amount)
  When Source Not Matched Then
      Update Set CustomerTotal = 0;

11. The first line contains the target table (TblCustomer).

12. The second contains the source table selection where I have a select statement from the TblOrder renamed as OrderSrc.

13. When the two tables have something in common, the order amount is added to the customer total.

14. When there is a record in the Order and no record inside the customer table, a new customer is created automatically with the amount.

15. When there is no record in order table, the customer total in the source table is initialized to zero.

Summary

Although the Merge Statement is very powerful and should be used whenever there is need, I do suggest that you do a lot of testing on it before you start using it. Also, you have to be really careful when using the merge statement about the identity column (you may be skipping ids or creating unrelated records in the target table). As a safety rule, the target table should not have an identity column as a foreign key inside the source table. See you in the next article with a new feature.



User Comments

No comments posted yet.






Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2019 ASPAlliance.com  |  Page Processed at 2019-07-16 12:50:08 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search