Get the Affected Data with the OUTPUT Keyword
November 16th, 2006 by Ivan Uzunov
If you have created Data Manipulation Language (DML) triggers in SQL Server, you are aware that you can retrieve information from special tables named inserted and deleted to view the new or previous version of rows. SQL Server 2005 introduces the OUTPUT keyword to provide this same functionality in a simple INSERT, UPDATE, and DELETE statement. You can use the OUTPUT keyword to return information about the results of a Transact-SQL statement into a table-valued variable.
You can include an OUTPUT clause in any DML statement with the following exceptions:
- INSERT statements targeting views
- DML operations with remote tables or views
- DML operations on local or distributed partitioned views
This is and example how OUTPUT could be used:
MS SQL Server 2005, OUTPUT, T SQL– declare temp table to store the affected by products delete categories
DECLARE @tmpAffectedCategories TABLE (category_id int)– delete all products with product_type_id = 2
– and insert the product category_id into the temp table
DELETE FROM Products
OUTPUT deleted.category_id INTO @tmpAffectedCategories
WHERE product_type_id = 2– do something with the categories (ex: update the products count into Categories table). I will just list the data.
SELECT * FROM @tmpAffectedCategories
I think this is one of my favorite T-SQL additions/mods in SQL 2005. It’s soooo helpful