In case you haven’t come across this yet. The OUTPUT clause allows you to display or manipulate rows affected by INSERT/UPDATE/DELETE, similar to the inserted and deleted virtual tables in a DML trigger.

   1:  
   2: CREATE TABLE t (id INT)
   3: GO
   4:  
   5: INSERT INTO t VALUES(1)
   6: INSERT INTO t VALUES(2)
   7: INSERT INTO t VALUES(3)
   8: INSERT INTO t VALUES(4)
   9: GO
  10:  
  11: -- this displays what was deleted
  12: DELETE t
  13:        OUTPUT deleted.id AS 'deleted';
  14:  
  15: -- this displays what is inserted
  16: INSERT INTO t
  17:     OUTPUT inserted.id AS 'inserted'
  18: VALUES(1)

VN:F [1.9.22_1171]
Rating: 9.5/10 (2 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)
How to Use the OUTPUT Clause (SQL Server 2005/2008), 9.5 out of 10 based on 2 ratings  
Be Sociable, Share!
  • Tweet

Related posts:

  1. Using the OUTPUT clause in SQL Server 2005 …
  2. SQL Server 2005/2008 System Views Map …
  3. Diagnostic Information Queries for SQL Server 2005/2008 …
  4. WMIProviderException when installing SSRS 2005 on Windows 2008 …
  5. SQL Server 2005 Cheat Sheet …
  6. Executing Batches Multiple Times (SQL Server 2005 only) …

Yarpp