Returns information from, or expressions based on, each row affected by an INSERT, UPDATE, DELETE, or MERGE statement.These results can be returned to the processing application for use in such things as confirmation messages, archiving, and other such application requirements.
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.An UPDATE, INSERT, or DELETE statement that has an OUTPUT clause will return rows to the client even if the statement encounters errors and is rolled back.The result should not be used if any error occurs when you run the statement.Is an alternative name used to reference the column name. DELETED Is a column prefix that specifies the value deleted by the update or delete operation. INSERTED cannot be used with the OUTPUT clause in the DELETE statement.
Is a column prefix that specifies a table included in the FROM clause of a DELETE, UPDATE, or MERGE statement that is used to specify the rows to update or delete. First Name; SELECT Employee ID, Last Name, First Name, Current Sales, Projected Sales FROM @My Table Var; GO SELECT Employee ID, Last Name, First Name, Current Sales, Projected Sales FROM dbo.
If the table being modified is also specified in the FROM clause, any reference to columns in that table must be qualified with the INSERTED or DELETED prefix.* Specifies that all columns affected by the delete, insert, or update action will be returned in the order in which they exist in the table. Employee Sales; GO USE Adventure Works2012; GO DECLARE @My Table Var table ( Product ID int NOT NULL, Product Name nvarchar(50)NOT NULL, Product Model ID int NOT NULL, Photo ID int NOT NULL); DELETE Production.
Columns prefixed with DELETED reflect the value before the UPDATE, DELETE, or MERGE statement is completed. Product ID BETWEEN 800 and 810; --Display the results of the table variable. Order Date = '20070401' GROUP BY Product ID) AS src (Product ID, Order Qty) ON (pi.
DELETED cannot be used with the OUTPUT clause in the INSERT statement. SELECT Product ID, Product Name, Photo ID, Product Model ID FROM @My Table Var; GO USE Adventure Works2012; GO IF OBJECT_ID(N'Production. Zero Inventory (Deleted Product ID int, Removed On Date Date Time); GO INSERT INTO Production.
INSERTED Is a column prefix that specifies the value added by the insert or update operation. Zero Inventory', N'U') IS NOT NULL DROP TABLE Production. Zero Inventory (Deleted Product ID, Removed On Date) SELECT Product ID, GETDATE() FROM ( MERGE Production.
Columns prefixed with INSERTED reflect the value after the UPDATE, INSERT, or MERGE statement is completed but before triggers are executed. Product Photo ID INTO @My Table Var OUTPUT DELETED. Product Photo ID, GETDATE() AS Deleted Date FROM Production. Product Inventory AS pi USING (SELECT Product ID, SUM(Order Qty) FROM Sales.