Output keyword in Sql Server 2008

Sql Server 2005 & 2008 has a new output clause. you can add an OUTPUT clause to your data manipulation language (DML) statements. The clause returns a copy of the data that you've inserted into or deleted from your tables. You can return that data to a table variable, a temporary or permanent table, or to the processing application that's calls the DML statement. You can then use that data for such purposes as archiving, confirmation messaging, or other application requirements.

In a simple word we can say OUTPUT clause  used with INSERT, UPDATE, or DELETE to identify the actual rows affected by these statements and also used for the auditing purpose.

The basic syntax of output keyword is

INSERT INTO TableName
OUTPUT INSERTED.Columns
VALUES(Value_1, Value_2, Value X)

If you want to try the examples in this article, you should first run the following script to create the Person table in a SQL Server database:

create table person

(
  id int primary key,
  fname varchar(10) not null,
  lname varchar(10) not null,
   city varchar(10) not null
)

Using an OUTPUT Clause in an INSERT Statement

When you insert data into a table, you can use the OUTPUT clause to return a copy of the data that's been inserted into the table. The OUTPUT clause takes two basic forms: OUTPUT and OUTPUT INTO. Use the OUTPUT form if you want to return the data to the calling application. Use the OUTPUT INTO form if you want to return the data to a table or a table variable.
For most of these examples, I return the outputted data only to a table variable.

--this will insert  record from table and display the inserted record

insert into person
output inserted.id,inserted.fname,inserted.lname,inserted.city
values(1,'akshay','choudhary','meerut')

for further process I adding some more data in the person table

--add more record into table
insert into person values(2,'deepak','dwij','noida')
insert into person values(3,'ranjeet','singh','ghaziabad')

Using an OUTPUT Clause in an UPDATE Statement

In the previous examples, the OUTPUT clause includes the INSERTED column prefix in the OUTPUT subclause. However, the OUTPUT clause supports a second column prefix—DELETED. The DELETED prefix returns the values that have been deleted from a table.
This is important because an UPDATE operation is actually two operations—a deletion and an insertion. As a result, you use both the INSERTED and DELETED column prefixes when adding an OUTPUT clause to an UPDATE statement

--this will update record into table and display the updated record

 update person
set
    fname='atul',
    lname='Kumar'
output
    inserted.id,
    deleted.fname,
    deleted.lname,
    inserted.city
where
id=3  

Using an OUTPUT Clause in a DELETE Statement

In a DELETE statement, you add the OUTPUT clause between the DELETE and WHERE clauses.

--this will delete a record from table and display the deleted record 

delete from person
output deleted.id,deleted.fname,deleted.lname,deleted.city

where id=3 

      

Using OUTPUT Clause for making backup of data into a table

--create table  taking backup of deleted table

 select * into personbackup from person where 1=0

--one empty table is created

--- Capture the deleted record in the AccountsBackUp table -

delete from person
output deleted.id,deleted.fname,deleted.lname,deleted.city
into personbackup
where id=2

--insert a record into personbackup

insert into person
output inserted.id,inserted.fname,inserted.lname,inserted.city
into personbackup
values(3,'amrit','choudhary','meerut')

select * from person

 

select * from personbackup

 

-- Adding COLUMN to maintain status of record inserted, deleted, updated in the table --

 alter table personbackup add status varchar(10)

 

 select * from personbackup

 

 --- adding record status as 'Inserted' [INTO is optional for storage] ---

insert into person
output inserted.id,inserted.fname,inserted.lname,inserted.city,
'inserted'
into personbackup
values(4,'arjun','panwar','shamli')
go

 

select * from personbackup

 

 -- In the deleted query, the INSERTED.Name is written which throws Error --

delete from person
output deleted.id,inserted.fname,deleted.lname,deleted.city
into personbackup

where id=2

  Conclusion :

  1. In DELETE query we can work with only DELETED
  2. In INSERT query we can work with only INSERTED
  3. In UPDATE query we can work with both INSERTED as well as DELETED

Reference:

http://www.simple-talk.com/sql/learn-sql-server/implementing-the-output-clause-in-sql-server-2008/