The OUTPUT clause is a very powerful feature of INSERT, UPDATE, DELETE, and MERGE
DML statements. This clause allows you to return information about the
data affected by a DML statement. For example, when you update a table,
you can return the affected rows and their values before and after modification. You can insert the rows returned by the OUTPUT statement into a table, or a table-valued variable using the OUTPUT INTO statement. Examples 1 and 2 show the syntax of the OUTPUT and OUTPUT INTO clauses.
Example 1. OUTPUT Clause—Syntax
OUTPUT [INSERTED.Column_name, ...][DELETED.Column_name,...][Scalar_expression |
Variable | From_clause_table.Column_name]
|
Example 2. OUTPUT INTO Clause—Syntax
OUTPUT [INSERTED.Column_name, ...][DELETED.Column_name,...][Scalar_expression |
Variable | From_clause_table.Column_name] INTO output_table | @table_variable
[(Column_list)]
|
How do you reference values that are old and new? These values are
stored in special tables: INSERTED and DELETED. These are the same
INSERTED and DELETED tables you used when learning about triggers. The
INSERTED table contains new values for the rows that have been affected
by the UPDATE statement. It also contains values of rows that have been added by the INSERT statement. The DELETED table contains old values for the rows that have been affected by an UPDATE statement. It also contains values of rows that have been deleted by the DELETE statement. You cannot use the INSERTED table with the OUTPUTDELETE statement. clause of a Likewise, you cannot use the DELETED table with the OUTPUT clause of an INSERT statement. You can use both tables with the OUTPUT clause on an UPDATE statement.
You can specify INSERTED.Column_name, DELETED.Column_name, INSERTED., and DELETED. in the OUTPUT clause. You can also specify From_clause_table.Column_name. This is any column from a table mentioned in the FROM
clause, except the table affected by the DML statement. Finally, you
can specify a variable or a scalar expression. When you use variables in
the OUTPUT clause, and these variables were modified by the DML statement, the clause will always return the original variable values.
When using the OUTPUT INTO clause you can specify a table or table-valued variable. This table will accept the results returned by the OUTPUT clause.
This table or variable must have the same column structure as values returned by the OUTPUT INTO clause. You cannot use the following types of tables with the OUTPUT INTO clause:
Remote tables Views Common table expressions Tables with triggers Tables participating in FOREIGN KEY constraints Tables participating in merge replication, or transactional replication with updateable subscribers
Let’s examine some examples of using the OUTPUT clause with INSERT, UPDATE, and DELETE DML statements. Performing these examples yourself will familiarize you with the OUTPUT clause (see Examples 3 through 7).
Example 3. Using the INSERT Statement with the OUTPUT Clause
CREATE TABLE FarmAnimals
(AnimalID int IDENTITY PRIMARY KEY,
AnimalName nvarchar(50),
Price money)
GO
INSERT FarmAnimals (AnimalName, Price)
OUTPUT inserted.AnimalID as ins_animal_ID, inserted.AnimalName as ins_animal_
name, inserted.Price ins_price
VALUES (′Goat′, 250),(′Sheep′, 300)
GO
-- Results:
-- ins_animal_ID ins_animal_name ins_price
-- ------------- --------------- ---------
-- 1 Goat 250.00
-- 2 Sheep 300.00
|
Example 4. Using the UPDATE Statement with the OUTPUT Clause
UPDATE FarmAnimals
SET AnimalName = ′Llama′, Price = 3000
OUTPUT
inserted.AnimalID as ins_animal_ID, inserted.AnimalName as ins_animal_name,
inserted.Price ins_price,
deleted.AnimalID as del_animal_ID, deleted.AnimalName as del_animal_name,
deleted.Price del_price
WHERE AnimalID = 2
GO
-- Results:
-- ins_animal_ID ins_animal_name ins_price
-- ------------- --------------- ---------
-- 2 Llama 3000.00
-- Results (CONTINUED):
-- del_animal_ID del_animal_name del_price
-- ------------- --------------- ---------
-- 2 Sheep 300.00
|
Example 5. Using the DELETE Statement with the OUTPUT Clause
DELETE FarmAnimals
OUTPUT
deleted.AnimalID as del_animal_ID, deleted.AnimalName as del_animal_name,
deleted.Price del_price
WHERE AnimalID = 1
GO
-- Results:
-- del_animal_ID del_animal_name del_price
-- ------------- --------------- ---------
-- 1 Goat 250.00
|
Example 6. Using the UPDATE Statement with the OUTPUT INTO Clause
DECLARE @ResultTable TABLE
(LocationName nvarchar(50),
NewRate smallmoney,
OldRate smallmoney,
RateChange smallmoney)
UPDATE Production.Location
SET CostRate = 25.00
OUTPUT inserted.Name,
inserted.CostRate,
deleted.CostRate,
deleted.CostRate - inserted.CostRate
INTO @ResultTable
WHERE LocationID = 1
SELECT * FROM @ResultTable
GO
-- Results:
-- LocationName NewRate OldRate RateChange
-- ------------ ------- ------- ----------
-- Tool Crib 25.00 0.00 –25.00
|
Example 7. Using the OUTPUT INTO Clause with an Existing Table and a FROM Join
CREATE TABLE DeprecatedProducts (ProductID int, ProductName varchar(50),
OriginalPrice money, OrderDate datetime)
GO
UPDATE TOP (2) Production.Product SET DiscontinuedDate = GETDATE(),
ListPrice = 0
OUTPUT inserted.ProductID, inserted.Name, deleted.ListPrice, SalesOrderHeader.
OrderDate INTO DeprecatedProducts
FROM Production.Product join Sales.SalesOrderDetail
ON Product.ProductID = SalesOrderDetail.ProductID
join Sales.SalesOrderHeader ON
SalesOrderDetail.SalesOrderID = SalesOrderHeader.SalesOrderID
SELECT * from DeprecatedProducts
GO
-- Results:
-- ProductID ProductName OriginalPrice OrderDate
-- --------- ----------- ------------- ---------
-- 749 Road-150 Red, 62 3578.27 2001-07-06 00:00:00.000
-- 751 Road-150 Red, 48 3578.27 2001-07-07 00:00:00.000
|
Warning
Remember that if the DML statement fails, for example, due to a constraint violation, the OUTPUT clause would still return the rows that would have been inserted or deleted. Therefore, you should never rely on the OUTPUT
statement to indicate that the statement was successful. Your database
application should check for errors from DML statements. If the
application encounters an error, the OUTPUT results should not be used.
|
