Output Clause in Sql Server

What is the use of Output Clause ?

The OUTPUT clause returns a copy of the data that you have inserted into or Deleted from your tables and you can refer the copy of your data by using Inserted.columnname &nbsp (or) Deleted.columname in a table variable (or) Temporary table or Permanent table and also you can use for the purpose of confirmation messaging or any other requirements.

Where we can use the Output Clause ?

OUTPUT clause can be used with INSERT, UPDATE, or DELETE statements to identify the actual rows affected by these sql statements.

How many Forms the Output Clause is existed and where it's use ?

The OUTPUT clause is existed in two forms :
1.   OUTPUT
2.   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.

Using an OUTPUT Clause in an INSERT Statement

The OUTPUT clause is placed between the INSERT and VALUES clause. Because I’m outputting the data to a variable, the OUTPUT clause includes two parts: the OUTPUT subclause and the INTO subclause and the process for returning data to a table is the same as that for a table variable and also temporary table.

Example of "Output Into " Clause with insert statement

CREATE TABLE TestTable (StudentId INT, StudentName VARCHAR(100) ,marks INT)

----Creating temp table to store the values return by OUTPUT clause
DECLARE @TmpTable TABLE (ID INT, TEXTVal VARCHAR(100))
----Insert values in real table as well use OUTPUT clause to insert
----values in the temp table.
INSERT &nbsp TestTable &nbsp (ID, TEXTVal)
OUTPUT Inserted.ID, Inserted.TEXTVal INTO @TmpTable
VALUES (1,'FirstVal')
INSERT &nbsp TestTable &nbsp (ID, TEXTVal)
OUTPUT Inserted.ID, Inserted.TEXTVal INTO @TmpTable
VALUES (2,'SecondVal')
----Check the values in the temp table and real table
----The values in both the tables will be same
SELECT * FROM @TmpTable
SELECT * FROM TestTable
Both SQL Server 2005 and 2008 let you add an OUTPUT clause to INSERT, UPDATE, or DELETE statements. SQL Server 2008 also lets you add the clause to MERGE statements. OUTPUT clause can be used with INSERT, UPDATE, or DELETE statements to identify the actual rows affected by these sql statements. you can parallely insert the values two tables at a time with the help of output clause