What is output cluase ?
- OUTPUT cluase returns the information of all the rows affected by the INSERT,UPDATE,DELETE and MERGE staments.
- The results can also be inserted into a table or table variable. Additionally, you can capture the results of an OUTPUT clause in a nested INSERT, UPDATE, DELETE, or MERGE statement, and insert those results into a target table or view.
Why it is used ?
- The information returned from the OUTPUT clause can be used as confirmation purposes, archiving, change capture and some other application requirements
Can be used in
- INSERT
- UPDATE
- DELETE
- MERGE
lets get into more details of how to use it
CREATE TABLE #TEMP(ID INT,VAL VARCHAR(100))
INSERT INTO #TEMP(ID,VAL)
OUTPUT INSERTED.*
VALUES (1,‘XYZ’),
(2,‘XYX’),
(3,‘AQW’)
DELETE
FROM #TEMP
OUTPUT DELETED.*
WHERE ID = 1;
UPDATE #TEMP
SET VAL = ‘WWW’
OUTPUT
INSERTED.*,DELETED.*
WHERE ID = 3
SELECT *FROM #TEMP
Some times when using the identity columns in the tables there will be a need for the applications interacting with the database to know the current identity values for the inserted columns
CREATE TABLE #TEMP(ID INT identity ,VAL VARCHAR(100))
INSERT INTO #TEMP(VAL)
OUTPUT INSERTED.*
VALUES ('XYZ'),
('XYX'),
('AQW')
- Using OUTPUT INTO to log the changes into another table
USE AdventureWorks2008R2;
GO
DECLARE @MyTableVar table( NewScrapReasonID smallint,
Name varchar(50),
ModifiedDate datetime);
INSERT Production.ScrapReason
OUTPUT INSERTED.ScrapReasonID, INSERTED.Name, INSERTED.ModifiedDate
INTO @MyTableVar
VALUES (N'Operator error', GETDATE());
--Display the result set of the table variable.
SELECT NewScrapReasonID, Name, ModifiedDate FROM @MyTableVar;
--Display the result set of the table.
SELECT ScrapReasonID, Name, ModifiedDate
FROM Production.ScrapReason;
GO
- Using OUTPUT clause in MERGE
-
Inserting the results of the MERGE statement into another table
USE ADVENTUREWORKS2008R2;
GO
CREATE TABLE PRODUCTION.UPDATEDINVENTORY
(PRODUCTID INT NOT NULL, LOCATIONID INT, NEWQTY INT, PREVIOUSQTY INT,
CONSTRAINT PK_INVENTORY PRIMARY KEY CLUSTERED (PRODUCTID, LOCATIONID));
GO
INSERT INTO PRODUCTION.UPDATEDINVENTORY
SELECT PRODUCTID, LOCATIONID, NEWQTY, PREVIOUSQTY
FROM
( MERGE PRODUCTION.PRODUCTINVENTORY AS PI
USING (SELECT PRODUCTID, SUM(ORDERQTY)
FROM SALES.SALESORDERDETAIL AS SOD
JOIN SALES.SALESORDERHEADER AS SOH
ON SOD.SALESORDERID = SOH.SALESORDERID
AND SOH.ORDERDATE BETWEEN '20080701' AND '20080731'
GROUP BY PRODUCTID) AS SRC (PRODUCTID, ORDERQTY)
ON PI.PRODUCTID = SRC.PRODUCTID
WHEN MATCHED AND PI.QUANTITY - SRC.ORDERQTY >= 0
THEN UPDATE SET PI.QUANTITY = PI.QUANTITY - SRC.ORDERQTY
WHEN MATCHED AND PI.QUANTITY - SRC.ORDERQTY <= 0
THEN DELETE
OUTPUT $ACTION, INSERTED.PRODUCTID, INSERTED.LOCATIONID, INSERTED.QUANTITY AS NEWQTY, DELETED.QUANTITY AS PREVIOUSQTY)
AS CHANGES (ACTION, PRODUCTID, LOCATIONID, NEWQTY, PREVIOUSQTY) WHERE ACTION = 'UPDATE';
GO
SELECT * FROM PRODUCTION.UPDATEDINVENTORY
DROP TABLE PRODUCTION.UPDATEDINVENTORY
Below is some use full information about OUTPUT clause where we can use and where we can not
- The OUTPUT clause may be useful to retrieve the value of identity or computed columns after an INSERT or UPDATE operation.
- When a computed column is included in the <dml_select_list>, the corresponding column in the output table or table variable is not a computed column. The values in the new column are the values that were computed at the time the statement was executed.
- There is no guarantee that the order in which the changes are applied to the table and the order in which the rows are inserted into the output table or table variable will correspond.
- If parameters or variables are modified as part of an UPDATE statement, the OUTPUT clause always returns the value of the parameter or variable as it was before the statement executed instead of the modified value.
You can use OUTPUT with an UPDATE or DELETE statement positioned on a cursor that uses WHERE CURRENT OF syntax.
The OUTPUT clause is not supported in the following statements:
- DML statements that reference local partitioned views, distributed partitioned views, or remote tables.
- INSERT statements that contain an EXECUTE statement.
- Full-text predicates are not allowed in the OUTPUT clause when the database compatibility level is set to 100.
- The OUTPUT INTO clause cannot be used to insert into a view, or rowset function.
- A user-defined function cannot be created if it contains an OUTPUT INTO clause that has a table as its target.
To prevent nondeterministic behavior, the OUTPUT clause cannot contain the following references:
- Subqueries or user-defined functions that perform user or system data access, or are assumed to perform such access. User-defined functions are assumed to perform data access if they are not schema-bound.
- A column from a view or inline table-valued function when that column is defined by one of the following methods:
- A subquery.
- A user-defined function that performs user or system data access, or is assumed to perform such access.
- A computed column that contains a user-defined function that performs user or system data access in its definition.
When SQL Server detects such a column in the OUTPUT clause, error 4186 is raised. For more information, see MSSQLSERVER_4186.
Inserting Data Returned From an OUTPUT Clause Into a Table
When you are capturing the results of an OUTPUT clause in a nested INSERT, UPDATE, DELETE, or MERGE statement and inserting those results into a target table, keep the following information in mind:
- The whole operation is atomic. Either both the INSERT statement and the nested DML statement that contains the OUTPUT clause execute, or the whole statement fails.
- The following restrictions apply to the target of the outer INSERT statement:
- The target cannot be a remote table, view, or common table expression.
- The target cannot have a FOREIGN KEY constraint, or be referenced by a FOREIGN KEY constraint.
- Triggers cannot be defined on the target.
- The target cannot participate in merge replication or updatable subscriptions for transactional replication.
- The following restrictions apply to the nested DML statement:
- The target cannot be a remote table or partitioned view.
- The source itself cannot contain a <dml_table_source> clause.
- The OUTPUT INTO clause is not supported in INSERT statements that contain a <dml_table_source> clause.
- @@ROWCOUNT returns the rows inserted only by the outer INSERT statement.
- @@IDENTITY, SCOPE_IDENTITY, and IDENT_CURRENT return identity values generated only by the nested DML statement, and not those generated by the outer INSERT statement.
- Query notifications treat the statement as a single entity, and the type of any message that is created will be the type of the nested DML, even if the significant change is from the outer INSERT statement itself.
- In the <dml_table_source> clause, the SELECT and WHERE clauses cannot include subqueries, aggregate functions, ranking functions, full-text predicates, user-defined functions that perform data access, or the TEXTPTR function.
Triggers
Columns returned from OUTPUT reflect the data as it is after the INSERT, UPDATE, or DELETE statement has completed but before triggers are executed.
For INSTEAD OF triggers, the returned results are generated as if the INSERT, UPDATE, or DELETE had actually occurred, even if no modifications take place as the result of the trigger operation. If a statement that includes an OUTPUT clause is used inside the body of a trigger, table aliases must be used to reference the trigger inserted and deleted tables to avoid duplicating column references with the INSERTED and DELETED tables associated with OUTPUT.
If the OUTPUT clause is specified without also specifying the INTO keyword, the target of the DML operation cannot have any enabled trigger defined on it for the given DML action. For example, if the OUTPUT clause is defined in an UPDATE statement, the target table cannot have any enabled UPDATE triggers.
If the sp_configure option disallow results from triggers is set, an OUTPUT clause without an INTO clause causes the statement to fail when it is invoked from within a trigger.
Data Types
The OUTPUT clause supports the large object data types: nvarchar(max), varchar(max), varbinary(max), text, ntext, image, and xml. When you use the .WRITE clause in the UPDATE statement to modify an nvarchar(max), varchar(max), or varbinary(max)column, the full before and after images of the values are returned if they are referenced. The TEXTPTR( ) function cannot appear as part of an expression on a text, ntext, or image column in the OUTPUT clause.
Thanks!
Sqljunkieshare 