Output clause in SQL Server

Level: Beginners [level 100]

Output   clause gives access to Inserted, updated or deleted rows of table. In SQL Server 2005 this was possible only through Triggers.

Note: In all explanation, I am going to use School Database. You can download script from here

Imagine a scenario that while inserting a value in Person table, for the audit purpose you need to insert value FirstName column and insertion date in audit table. This was possible in SQL Server through Triggers. Whereas in SQL Server 2008 we can achieve this using Output clause  Smile

I created a table called Audit_Person in school database.

image

image

Now while inserting in Person table, I need to insert a row in this table also.

image

In above statement if you notice we are fetching inserted first name using Inserted.FirstName and current date using GetDate and inserting in Audit_Person table.

Let us say you have another scenario in which

  1. You want to delete all the person First Name started with P
  2. Insert First Name of deleted row with deleted date in Audit_Person table

To write above query you can use Output clause.

image

Scripts used in above explanation are below. Feel free to use them  Smile


Create  table Audit_Person
         (PersonName nvarchar(max),
          DateOfEntry date);

Insert into Person(LastName ,FirstName)
    Output Inserted.FirstName,
           GetDate()
    into Audit_Person
    values
		('Dave','Pinal');

Delete from Person
  Output deleted.Firstname ,
          GetDate()
   Into audit_person
 where FirstName like 'P%';

I hope this post was useful. Thanks for reading  Smile

6 thoughts on “Output clause in SQL Server

  1. It fetches the inserted or deleted row of the table. IN SQL Server 2005 it was only possible through Triggers

  2. HI everyone .. I am sorry for Title. Yes it was present in 2005 also. I have changed the title and modified the blog. Thanks .. I hope I will do my level best on coming SQL Server posts

  3. Pingback: Monthly Report June 2011: Total Posts 33 « debug mode……

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