How to Use the OUTPUT Clause (SQL Server 2005/2008)

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)

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s