programming4us
programming4us
DATABASE

SQL Server 2008 : Returning Data from DML Operations Using the OUTPUT Clause

- 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:14:30 AM
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.

Other  
  •  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
  •  Programming Microsoft SQL Server 2005: Using Data Mining Extensions (part 1) - Data Mining Modeling 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