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

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.