programming4us
programming4us
DATABASE

SQL Server 2008 : Using the OUTPUT Clause with the MERGE Statement

- How To Install Windows Server 2012 On VirtualBox
- How To Bypass Torrent Connection Blocking By Your ISP
- How To Install Actual Facebook App On Kindle Fire
1/19/2011 11:16:41 AM
The OUTPUT clause is of great value when used with the MERGE statement. Because the MERGE statement can perform multiple actions depending on the results of a join, it is hard to tell what data was updated, what data was inserted, and what data was deleted. The OUTPUT clause can return this information. The OUTPUT clause is specified for the entire MERGE statement, not each individual action.

Additionally, you can output the $action value. This value is available only for the MERGE statement. It stores the action performed on a particular row. The $action value is of nvarchar(10) type, and can hold only the following values:

  • INSERT

  • UPDATE

  • DELETE

Let’s examine some examples of using the OUTPUT clause with the MERGE statement. Again, be sure to perform these examples yourself against the AdventureWorks database (see Example 1).

Example 1. Using the MERGE Statement with the OUTPUT Clause
CREATE TABLE FarmAnimals
(AnimalID int IDENTITY PRIMARY KEY,
AnimalName nvarchar(50),
Price money)
GO

CREATE TABLE Pets
(AnimalID int IDENTITY PRIMARY KEY,
AnimalName nvarchar(50),
Price money)
GO

INSERT FarmAnimals (AnimalName, Price)
VALUES (′Goat′, 250),(′Sheep′, 300)
GO

INSERT Pets (AnimalName, Price)
VALUES (′Kitten′, 75),(′Puppy′, 120), (′Goat′, 350)
GO

MERGE INTO Pets
USING FarmAnimals ON Pets.AnimalName = FarmAnimals.AnimalName
WHEN MATCHED THEN
UPDATE SET Price = FarmAnimals.Price
WHEN NOT MATCHED BY TARGET THEN
INSERT (AnimalName, Price) VALUES (FarmAnimals.AnimalName, FarmAnimals.Price)
OUTPUT $action, inserted.AnimalName as ins_name, deleted.AnimalName as del_
name, deleted.Price as old_price;
--Results:
-- $action ins_name del_name old_price
-- ------- -------- -------- ---------
-- INSERT Sheep NULL NULL
-- UPDATE Goat Goat 350.00


Tip

Practice using the OUTPUT clause with the MERGE statement until you can use it confidently. Because the MERGE statement is new in SQL Server 2008, you are likely to be asked about its use in the exam. The use of the OUTPUT clause with all DML statements is one of the exam objectives.

Other  
  •  SQL Server 2008 : Returning Data from DML Operations Using the OUTPUT Clause
  •  SQL Server 2008: Working with System Databases
  •  SQL Server 2008 : Using @@IDENTITY and NEWID Functions in DML Statements
  •  SQL Server 2008 : Using Advanced Functionality with DML - Introduction
  •  Defensive Database Programming with SQL Server: The Ticket-Tracking System (part 2) - Removing the performance hit of ON UPDATE CASCADE
  •  Defensive Database Programming with SQL Server: The Ticket-Tracking System (part 1) - Enforcing business rules using constraints only
  •  SQL Server 2008 : Working with DML Queries - Using the MERGE Statement
  •  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
  •  
    Top 10
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Finding containers and lists in Visio (part 2) - Wireframes,Legends
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Finding containers and lists in Visio (part 1) - Swimlanes
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Formatting and sizing lists
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Adding shapes to lists
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Sizing containers
    - Microsoft Access 2010 : Control Properties and Why to Use Them (part 3) - The Other Properties of a Control
    - Microsoft Access 2010 : Control Properties and Why to Use Them (part 2) - The Data Properties of a Control
    - Microsoft Access 2010 : Control Properties and Why to Use Them (part 1) - The Format Properties of a Control
    - Microsoft Access 2010 : Form Properties and Why Should You Use Them - Working with the Properties Window
    - Microsoft Visio 2013 : Using the Organization Chart Wizard with new data
    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)
    programming4us programming4us
    programming4us
     
     
    programming4us