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