Output Clause in SQL Server

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

Note: In the following, I am going to use a School Database. You can download the script for it here.

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

I created a table called Audit_Person in a school database.



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


In the above statement notice that we are fetching the inserted first name using Inserted.FirstName and current date using GetDate and inserting them into the Audit_Person table.

Let us say you have another scenario in which:

1. You want to delete all the persons with a First Name starting with P
2. Insert the First Names of deleted rows along with the deleted date into the Audit_Person table

To write that query you can use an Output clause.


The scripts used in the above explanation are below. Feel free to use them.
 
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 