\nSQL Server 2005 introduced the OUTPUT clause in DML statements. With the OUTPUT clause, you can output column values to a table variable, a table, or return these values to the user. You can even use multiple OUTPUT clauses to push the values into both a table variable and a table from the same statement.\n<\/p>\n
\nIn SQL Server 2008 there is an additional option. You can use your OUTPUT column values directly in an INSERT-SELECT statement. Here's what it would look like, using MERGE with an OUTPUT clause (and an example from one of the early webcasts):\n<\/p>\n
\nCREATE TABLE Stock (Stock VARCHAR(10) PRIMARY KEY, Qty INT CHECK (Qty > 0));
\nCREATE TABLE Trades (Stock VARCHAR(10) PRIMARY KEY, Delta INT CHECK (Delta <> 0));
\nCREATE TABLE AuditChanges (Action varchar(6), Stock VARCHAR(6), Qty INT);
\nGO\n<\/p>\n
\nINSERT Stock VALUES('MSFT', 10), ('BOEING', 5);
\nINSERT Trades VALUES('MSFT', 5), ('BOEING', -5), ('GE', 3);
\nGO\n<\/p>\n
\nINSERT INTO AuditChanges
\nSELECT * FROM
\n(
\nMERGE Stock S
\n USING Trades T
\n ON S.Stock = T.Stock
\n WHEN MATCHED AND (Qty + T.Delta = 0) THEN
\n DELETE
\n WHEN MATCHED THEN
\n UPDATE SET Qty += T.Delta
\n WHEN NOT MATCHED THEN
\n INSERT VALUES(Stock, T.Delta)
\n OUTPUT $action, T.Stock, inserted.Qty
\n) tab (action, stock, qty);
\nGO\n<\/p>\n
\nNotice how the OUTPUT clause requires a table alias (in this case "tab") and needs to name the columns returned from OUTPUT.<\/p>\n","protected":false},"excerpt":{"rendered":"
SQL Server 2005 introduced the OUTPUT clause in DML statements. With the OUTPUT clause, you can output column values to a table variable, a table, or return these values to the user. You can even use multiple OUTPUT clauses to push the values into both a table variable and a table from the same statement. […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[29,40],"tags":[],"class_list":["post-732","post","type-post","status-publish","format-standard","hentry","category-sql-server-2008","category-transact-sql"],"yoast_head":"\n