DATABASE

SQL Server 2008 : Working with DML Queries - Using the MERGE Statement

1/17/2011 2:50:00 PM
The MERGE statement is new in SQL Server 2008. It allows you to synchronize one table with another. This is achieved by performing INSERT, UPDATE, and DELETE operations on a target table based on the results of a join with a source table. Expect to be asked about the MERGE statement in the exam because it is a new feature of SQL Server 2008. The syntax elements shown in Example 7.18 appear most commonly in the MERGE statement.
Example 1. MERGE Statement—Core Elements
MERGE
[ TOP (n) [PERCENT] ]
[INTO] target_table
USING source_table ON merge_search_condition
[WHEN MATCHED [AND search_condition] THEN
matched_action]
[WHEN NOT MATCHED [BY TARGET] [AND search_condition] THEN
Not_matched_by_target_action]
[WHEN NOT MATCHED BY SOURCE [AND search_condition] THEN
Not_matched_by_source_action]

The MERGE keyword specifies that a MERGE operation is to be performed. The target_table is the table into which you will be merging the data. The USING source_table clause specifies the table or query from which you will be merging the data. You can also specify a VALUES expression as the source table. You must be able to join this table or query to the target table. This join between the source and target tables is how data matches will be established. You can alias both the source and destination tables using the as keyword. Although doing this is optional, this practice greatly helps the readability of your statement. Consider aliasing the target table as tgt, and the source table or query as src.

The ON merge_search_condition clause specifies the criteria of the join between the source and target tables. This join is how SQL Server determines whether the data in the destination table has been matched by the source table.

The TOP (n) [PERCENT] clause serves the same purpose as it does in the INSERT, UPDATE, and DELETE statements. If this clause is specified, only the top n, or top n percent of rows will be processed by the MERGE operation.

WHEN MATCHED, WHEN NOT MATCHED BY SOURCE, and WHEN NOT MATCHED BY TARGET specify the actions to be carried out when a match is established, or when data is found in one table that is not found in another. Valid actions are UPDATE, INSERT, and DELETE statements.

MERGE Statement Examples

Examples 2 through 4 demonstrate uses of the MERGE statement. We will start with a simpler example, then work our way through to more advanced examples. You can run any of these examples against the AdventureWorks database. Performing the examples yourself will give you a better understanding of how to use the MERGE statement.

Example 2. Using the MERGE Statement to Insert, Update, and Delete Data
USE AdventureWorks
GO

-- Create two tables, Produce and Fruit. Insert values into the tables.
CREATE TABLE Produce (Name nvarchar(20), Price smallmoney)
GO
CREATE TABLE Fruit (Name nvarchar(20), Price smallmoney)
GO
INSERT Produce VALUES ('Milk', 2.4), ('Honey', 4.99), ('Apples', 3.99),
('Bread', 2.45), ('Pears', 4.00)
GO
INSERT Fruit VALUES ('Apples', 6.00), ('Pears', 7.00), ('Bananas', 4.95),
('Mandarins', 3.95), ('Oranges', 2.50)
GO

-- Show records in both tables. Note that some records (Apples and Pears) are
in Produce and in Fruit, but prices differ. There are some records in Produce
that are not in Fruit, and some in Fruit that are not in Produce.
SELECT * FROM Produce
GO
SELECT * FROM Fruit
GO

-- Use MERGE to update prices in Produce for those records that exist in
Fruit. Insert records from Fruit that are not in Produce. Delete any records
that are not in Fruit from Produce.
MERGE INTO Produce
USING Fruit
ON (Produce.Name = Fruit.Name)
WHEN MATCHED THEN
UPDATE SET Price = Fruit.Price

WHEN NOT MATCHED BY TARGET THEN
INSERT (Name, Price) VALUES(Fruit.Name, Fruit.Price)
WHEN NOT MATCHED BY SOURCE THEN
DELETE;
GO

-- Show records in Produce. Note that prices for Apples and Pears have been
updated; Bananas, Oranges and Mandarins have been inserted; and all non-fruit
i.e. Milk, Honey etc. is deleted
SELECT * FROM Produce
GO

Example 3. Using the MERGE Statement with the VALUES Clause
USE AdventureWorks
GO

-- Show Data in HumanResources.Department table. Note that the Production
Control department is in the Manufacturing group. Note the number of
departments.
SELECT * FROM HumanResources.Department
GO

-- Use MERGE to update prices in Produce for those records that exist in
Fruit. Insert records from Fruit that are not in Produce. Delete any records
that are not in Fruit from Produce.
MERGE INTO HumanResources.Department as Target
USING (VALUES('Production Control', 'Inventory Management'), ('Building
Security', 'Maintenance Services'))
AS Source (DepartmentName, NewGroupName)
ON Target.Name = Source.DepartmentName
WHEN MATCHED THEN
UPDATE SET GroupName = Source.NewGroupName
WHEN NOT MATCHED BY TARGET THEN
INSERT (Name, GroupName) VALUES(Source.DepartmentName, Source.
NewGroupName);
GO

-- Show Data in HumanResources.Department again. Note that the Production
Control department is now in the Inventory Management group. Note that the
Building Security department was added.
SELECT * FROM HumanResources.Department
GO

Example 4. Using the MERGE Statement to Insert and Update Data
USE AdventureWorks
GO

-- Use the SELECT...INTO statement to create a new table named ScrapAudit, and
insert two values from the Production.ScrapReason table into it
SELECT Name
INTO dbo.ScrapAudit
FROM Production.ScrapReason
WHERE Name = 'Wheel misaligned' OR Name = 'Color incorrect'
GO

-- Add two new values to the ScrapAudit table
INSERT ScrapAudit Values ('Glue not set'), ('Item falling apart')
GO

-- Show values in ScrapAudit and Production.ScrapReason. Note that ScrapAudit
has two values that are also in the ScrapReason table, and two values that
are not.
SELECT * FROM ScrapAudit
GO
SELECT * FROM Production.ScrapReason
GO

-- Use the merge statement to insert values that don't exist in Production.
ScrapReason from ScrapAudit. Where values exist in both tables, update the
ModifiedDate in ScrapReason to be today's date
MERGE
INTO Production.ScrapReason
USING ScrapAudit
ON (ScrapReason.Name = ScrapAudit.Name)
WHEN MATCHED THEN
UPDATE SET ModifiedDate = GETDATE()
WHEN NOT MATCHED BY TARGET THEN
INSERT (Name, ModifiedDate) VALUES (ScrapAudit.Name, GETDATE());
GO

-- Show values in Production.ScrapReason. Note that the two new values are
added, and two existing values show ModifiedDate as today.
SELECT * FROM Production.ScrapReason
GO


Exercise . USING THE MERGE STATEMENT

In this exercise, we will practice using the MERGE statement in various scenarios.

Before you begin, you must have the following software installed on your computer:

  • SQL Server 2008 (a free trial is available for download)

  • AdventureWorks sample database

This exercise does not depend on you completing any previous exercises.

  1. Open SQL Server Management Studio. Click Start | All Programs | Microsoft SQL Server 2008 | SQL Server Management Studio.

  2. Create a new query against the AdventureWorks database.

  3. Create a table named ProductAudit containing the columns ProductID, ProductName, and ListPrice by executing the following statement.

    CREATE TABLE ProductAudit
    (ProductID int PRIMARY KEY, ProductName nvarchar(255),
    ListPrice money)
  4. Insert all products priced over 1,000 from Production.Product into ProductAudit. To do this, execute the following statement:

    INSERT INTO ProductAudit
    SELECT ProductID, Product.Name as ProductName, ListPrice
    FROM Production.Product
    WHERE ListPrice > 1000
  5. In the ProductAudit table, reduce the price for all products containing ‘Yellow’ in the name, to 80% of the original. To do this, execute the following statement:

    UPDATE ProductAudit SET ListPrice *= 0.8
    WHERE ProductName like '%Yellow%'
  6. Write and execute a MERGE statement targeting Production. Product performing the following actions:

    • Find all products that exist in both tables, but with different prices. Set the ListPrice for these products to be the value from ProductAudit.

    • Where products are not listed at all in ProductAudit, discontinue them by setting the DiscontinuedDate to today’s date.

    Your statement should look like this:

    MERGE INTO Production.Product
    USING ProductAudit ON Product.ProductID = ProductAudit.ProductID
    WHEN MATCHED AND Product.ListPrice <> ProductAudit.ListPrice THEN
    UPDATE SET ListPrice = ProductAudit.ListPrice
    WHEN NOT MATCHED BY SOURCE THEN
    UPDATE SET DiscontinuedDate = GETDATE();

    How many rows were updated by this MERGE statement? What would happen if you reran the statement?

  7. Write and execute a MERGE statement targeting ProductAudit performing the following actions:

    • Find all products that exist in both ProductAudit and Product, and add 20 to the list price of these products.

    • Find all products that exist in Product, but not in ProductAudit, and add them to the ProductAudit table.

    Your statement should look like this:

    MERGE INTO ProductAudit
    USING Production.Product ON ProductAudit.ProductID = Product.
    ProductID
    WHEN MATCHED THEN
    UPDATE SET ListPrice += 20.00
    WHEN NOT MATCHED BY TARGET THEN
    INSERT VALUES(Product.ProductID, Product.Name,
    Product.ListPrice)

    How many rows were updated by this MERGE statement? What would happen if you reran the statement?

Other  
  •  Defensive Database Programming with SQL Server : Client-side Error Handling
  •  SQL Server 2008 : Working with DML Queries - Using the DELETE Statement
  •  Programming Microsoft SQL Server 2005: Using Data Mining Extensions (part 2) - Data Mining Predictions Using DMX
  •  Programming Microsoft SQL Server 2005: Using Data Mining Extensions (part 1) - Data Mining Modeling Using DMX
  •  SQL Server 2008 : Working with DML Queries - Using the UPDATE Statement (part 2)
  •  SQL Server 2008 : Working with DML Queries - Using the UPDATE Statement (part 1)
  •  Azure Programming Considerations
  •  Programming with SQL Azure : Record Navigation in WCF Data Services
  •  Defensive Database Programming with SQL Server : TRY...CATCH Gotchas (part 2) - TRY...CATCH blocks cannot catch all errors
  •  Defensive Database Programming with SQL Server : TRY...CATCH Gotchas (part 1) - Re-throwing errors
  •  
    Video
    Top 10
    SG50 Ferrari F12berlinetta : Prancing Horse for Lion City's 50th
    The latest Audi TT : New angles for TT
    Era of million-dollar luxury cars
    Game Review : Hearthstone - Blackrock Mountain
    Game Review : Battlefield Hardline
    Google Chromecast
    Keyboards for Apple iPad Air 2 (part 3) - Logitech Ultrathin Keyboard Cover for iPad Air 2
    Keyboards for Apple iPad Air 2 (part 2) - Zagg Slim Book for iPad Air 2
    Keyboards for Apple iPad Air 2 (part 1) - Belkin Qode Ultimate Pro Keyboard Case for iPad Air 2
    Michael Kors Designs Stylish Tech Products for Women
    REVIEW
    - First look: Apple Watch

    - 3 Tips for Maintaining Your Cell Phone Battery (part 1)

    - 3 Tips for Maintaining Your Cell Phone Battery (part 2)
    Popular Tags
    Video Tutorail Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Exchange Server Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 Adobe Flash Professional Dreamweaver Adobe Illustrator Adobe Photoshop CorelDRAW X5 CorelDraw 10 windows Phone 7 windows Phone 8 Iphone