SQL Server 2005 introduces a new TSQL feature that allows you to retrieve data affected by insert/update/delete statements easily. This is achieved by the use of OUTPUT clause which can reference columns from the inserted and deleted tables (that are available from triggers currently) or expressions. The OUTPUT clause can be used to return results to the client or consume it on the server into a temporary table or table variable or permanent table.

Syntax

{ [ OUTPUT { { DELETED | INSERTED | fromtablename } . { * | columnname } | scalarexpression } [ [AS] columnaliasidentifier ] [ ,...n ] INTO { @tablevariable | outputtable } [ ( columnlist ) ] ] [ OUTPUT { { DELETED | INSERTED | fromtablename } . { * | columnname } | scalarexpression } [ [AS] columnalias_identifier ] [ ,...n ] ] }

@tablevariable Specifies a table variable that the returned rows are inserted into instead of being returned to the caller. @tablevariable must be declared before the INSERT, UPDATE, DELETE, or MERGE statement.

If columnlist is not specified, the table variable must have the same number of columns as the OUTPUT result set. The exceptions are identity and computed columns, which must be skipped. If columnlist is specified, any omitted columns must either allow null values or have default values assigned to them.

For more information about table variables, see table (Transact-SQL).

outputtable Specifies a table that the returned rows are inserted into instead of being returned to the caller. outputtable may be a temporary table.

If columnlist is not specified, the table must have the same number of columns as the OUTPUT result set. The exceptions are identity and computed columns. These must be skipped. If columnlist is specified, any omitted columns must either allow null values or have default values assigned to them.

output_table cannot:

Have enabled triggers defined on it. Participate on either side of a FOREIGN KEY constraint. Have CHECK constraints or enabled rules.

column_list Is an optional list of column names on the target table of the INTO clause. It is analogous to the column list allowed in the INSERT statement.

scalarexpression Is any combination of symbols and operators that evaluates to a single value. Aggregate functions are not permitted in scalarexpression. Any reference to columns in the table being modified must be qualified with the INSERTED or DELETED prefix.

columnaliasidentifier Is an alternative name used to reference the column name.

DELETED Is a column prefix that specifies the value deleted by the update or delete operation. Columns prefixed with DELETED reflect the value before the UPDATE, DELETE, or MERGE statement is completed. DELETED cannot be used with the OUTPUT clause in the INSERT statement.

INSERTED Is a column prefix that specifies the value added by the insert or update operation. Columns prefixed with INSERTED reflect the value after the UPDATE, INSERT, or MERGE statement is completed but before triggers are executed.

INSERTED cannot be used with the OUTPUT clause in the DELETE statement.

fromtablename Is a column prefix that specifies a table included in the FROM clause of a DELETE, UPDATE, or MERGE statement that is used to specify the rows to update or delete.

If the table being modified is also specified in the FROM clause, any reference to columns in that table must be qualified with the INSERTED or DELETED prefix.

* Specifies that all columns affected by the delete, insert, or update action will be returned in the order in which they exist in the table. OUTPUT DELETED.* in the following DELETE statement returns all columns deleted from the ShoppingCartItem table: Example: DELETE Sales.ShoppingCartItem OUTPUT DELETED.*;

Example Using OUTPUT INTO with a simple INSERT statement

USE AdventureWorks2008R2;

GO

DECLARE @MyTableVar table( NewScrapReasonID smallint, Name varchar(50), ModifiedDate datetime);

INSERT Production.ScrapReason

OUTPUT INSERTED.ScrapReasonID, INSERTED.Name, INSERTED.ModifiedDate

    INTO @MyTableVar

VALUES (N'Operator error', GETDATE());

--Display the result set of the table variable.

SELECT NewScrapReasonID, Name, ModifiedDate FROM @MyTableVar;

--Display the result set of the table.

SELECT ScrapReasonID, Name, ModifiedDate

FROM Production.ScrapReason;

GO

SQL Server 2005 introduces a new TSQL feature that allows you to retrieve data affected by insert/update/delete statements easily. This is achieved by the use of OUTPUT clause which can reference columns from the inserted and deleted tables (that are available from triggers currently) or expressions. The OUTPUT clause can be used to return results to the client or consume it on the server into a temporary table or table variable or permanent table.

use tempdb;
go
create table itest ( i int identity not null primary key, j int not null unique );
go
create trigger insert_itest on itest after insert
as
begin
    insert into #new ( i, j )
    select i, j
      from inserted;
end
go
create table #new ( i int not null, j int not null );
insert into itest ( j )
select o.id from sysobjects as o;
-- Newly inserted rows and identity values:
select * from #new;

-- #new can be used now to insert into a related table:
drop table #new, itest;
go

This code can be re-written in SQL Server 2005 using the OUTPUT clause like below:

create table itest ( i int identity not null primary key, j int not null unique )
create table #new ( i int not null, j int not null)

insert into itest (j)
output inserted.i, inserted.j into #new
select o.object_id from sys.objects as o

select * from #new
drop table #new, itest;
go

The OUTPUT clause is not supported in the following statements:

DML statements that reference local partitioned views, distributed partitioned views, or remote tables.

INSERT statements that contain an EXECUTE statement.

Full-text predicates are not allowed in the OUTPUT clause when the database compatibility level is set to 100.

The OUTPUT INTO clause cannot be used to insert into a view, or rowset function.

A user-defined function cannot be created if it contains an OUTPUT INTO clause that has a table as its target.

Subqueries or user-defined functions that perform user or system data access, or are assumed to perform such access. User-defined functions are assumed to perform data access if they are not schema-bound.

A column from a view or inline table-valued function when that column is defined by one of the following methods:

A subquery.

A user-defined function that performs user or system data access, or is assumed to perform such access.

A computed column that contains a user-defined function that performs user or system data access in its definition.

When SQL Server detects such a column in the OUTPUT clause, error 4186 is raised. For more information, see MSSQLSERVER_4186.

The OUTPUT clause supports the large object data types: nvarchar(max), varchar(max), varbinary(max), text, ntext, image, and xml. When you use the .WRITE clause in the UPDATE statement to modify an nvarchar(max), varchar(max), or varbinary(max) column, the full before and after images of the values are returned if they are referenced. The TEXTPTR( ) function cannot appear as part of an expression on a text, ntext, or image column in the OUTPUT clause.

Permissions

SELECT permissions are required on any columns retrieved through or used in .

INSERT permissions are required on any tables specified in .

Scenarios where we can use OUTPUT clause:

A. Using OUTPUT INTO with a simple INSERT statement

B. Using OUTPUT with a DELETE statement

C. Using OUTPUT INTO with an UPDATE statement

D. Using OUTPUT INTO to return an expression

E. Using OUTPUT INTO with fromtablename in an UPDATE statement

F. Using OUTPUT INTO with fromtablename in a DELETE statement

G. Using OUTPUT INTO with a large object data type

H. Using OUTPUT in an INSTEAD OF trigger

I. Using OUTPUT INTO with identity and computed columns

J. Using OUTPUT and OUTPUT INTO in a single statement

K. Inserting data returned from an OUTPUT clause

What is OUTPUT clause?

Returns information from, or expressions based on, each row affected by an INSERT, UPDATE, DELETE, or MERGE statement. These results can be returned to the processing application for use in such things as confirmation messages, archiving, and other such application requirements. The results can also be inserted into a table or table variable. Additionally, you can capture the results of an OUTPUT clause in a nested INSERT, UPDATE, DELETE, or MERGE statement, and insert those results into a target table or view.

You can use OUTPUT with an UPDATE or DELETE statement positioned on a cursor that uses WHERE CURRENT OF syntax.

Note: An UPDATE, INSERT, or DELETE statement that has an OUTPUT clause will return rows to the client even if the statement encounters errors and is rolled back. The result should not be used if any error occurs when you run the statement.

             Sometime we are using triggers to get old and new values or new values for the historical data to save in another table. So we can get inserted, updated and deleted data using triggers. 

But one amazing feature "Output" clause. This clause we are mostly use with stored procedures to get output variables. Output clause also can be used with insert, update and delete statements.

In SQL Server 2005 and 2008, 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.

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. In this article, I demonstrate how to include an OUTPUT clause in each one of these statements. I developed the examples used for the article on a local instance of SQL Server 2008. The examples also work in SQL Server 2005, except for those related to the MERGE statement.

You can read this artical Click

The OUTPUT clause is not supported in the following statements:

1) A target with a FOREIGN KEY constraint, or referenced by a FOREIGN KEY constraint, Triggers on the target,a target participating in replication 

2) DML statements that reference local partitioned views, distributed partitioned views, or remote tables.i.e A remote table, view, or common table expression as it’s target.  

3) INSERT statements that contain an EXECUTE statement.

4) Full-text predicates are not allowed in the OUTPUT clause when the database compatibility level is set to 100.

5) The OUTPUT INTO clause cannot be used to insert into a view, or rowset function.

6) A user-defined function cannot be created if it contains an OUTPUT INTO clause that has a table as its target.

7) Subqueries or user-defined functions that perform user or system data access, or are assumed to perform such access.User-defined functions are assumed to perform data access if they are not schema-bound

Besides the above, be careful with the behaviour of @@ROWCOUNT. It will only return the number of rows affected by the outer INSERT statement.

To prevent nondeterministic behavior, the OUTPUT clause cannot contain the following references:

            Subqueries or user-defined functions that perform user or system data access, or are assumed to perform such access. User-defined functions are assumed to perform data access if they are not schema-bound. 

            A column from a view or inline table-valued function when that column is defined by one of the following methods:

A subquery.

            A user-defined function that performs user or system data access, or is assumed to perform such access.

            A computed column that contains a user-defined function that performs user or system data access in its definition.

In SQL Server 2008, one of the new T-SQL enhancements makes this pretty simple. One can now define a table expression based off a modification statement with an OUTPUT clause and then apply the filters on it.For Example:

INSERT INTO dbo.PO_DTL (….)
SELECT ….
FROM (UPDATE dbo.INPT_PO_DTL
SET Quantity *= 1.5
OUTPUT
inserted.PO_DTL_ID,
deleted.Quantity AS Old_Qty,
inserted.Quantity AS New_Qty
WHERE RECVD_DATE > getdate() – 10) AS IV
WHERE IV.Old_Qty < 100.0 AND New_Qty >= 100.0
GO

One can also join that data set with another table and do processing. The biggest advantage (besides not having another table and another set of steps to achieve the same thing is the fact that we are reducing the number of records that we have to retrieve – previously, if the data set returned would have been large, we would have first stored it in a temp table and then applied a filter on it. If that data set was huge, it would have had performance implications. With this new feature, we can easily reduce that overhead and get only the records that we want/need.

Use Ouput clause for auditing perpose, One of the best use of output.

--we will keep "Audit Trail" for below given table
create table ClientOrder
(
      OrderID varchar(5)
      ,ClientID varchar(5)
      ,Test varchar(20)
      ,OrderDate datetime default getdate()
)
GO


--following is the table, we will keep "Audit Trail" in.
--this will keep track of all data changed
CREATE TABLE AuditOfOrder
(
      Id INT Identity(1,1)
      ,OrderID varchar(50)
      ,OldTest varchar(20)
      ,NewTest varchar(20)
      ,DMLPerformed varchar(15)
      ,ChangeDate datetime default getdate()
)
GO

--inserting data in "ClientOrder" table
--all insert will be stored in audit trail table too via "OUTPUT" clause
INSERT INTO ClientOrder(OrderID,ClientID,Test)
Output Inserted.OrderID,Inserted.Test,Inserted.Test,'Insert' into AuditOfOrder(OrderID,OldTest,NewTest,DMLPerformed)
VALUES('A1001','CHEM1','VOCMS Group1')
GO


--inserting data in "ClientOrder" table
--all insert will be stored in audit trail table too via "OUTPUT" clause
INSERT INTO ClientOrder(OrderID,ClientID,Test)
Output Inserted.OrderID,Inserted.Test,Inserted.Test,'Insert' into AuditOfOrder(OrderID,OldTest,NewTest,DMLPerformed)
VALUES('A1001','CHEM1','Pesticide Group1')
GO


--let us see what we have in both tables now.
select * from ClientOrder
select * from AuditOfOrder
go


--we will perform UPDATE on "ClientOrder" table
--which will be recorded in "AuditOfOrder" table too
INSERT INTO AuditOfOrder(OrderID,OldTest,NewTest,DMLPerformed)
SELECT
t.OrderID,
t.OldTest,
t.NewTest,
t.DMLPerformed
FROM
(
UPDATE ClientOrder
      SET Test ='SVOC Stars'
OUTPUT
      inserted.OrderID AS OrderID,
      deleted.Test AS OldTest,
      inserted.Test AS NewTest,
      'UPDATE' as DMLPerformed
WHERE ClientOrder.Test='VOCMS Group1'
) t
GO


--let us see what we have in both tables now.
select * from ClientOrder
select * from AuditOfOrder
go


--Finally the log of Delete will be stored in the same way.
INSERT INTO AuditOfOrder(OrderID,OldTest,NewTest,DMLPerformed)
SELECT
t.OrderID,
t.OldTest,
t.NewTest,
t.DMLPerformed
FROM
(
DELETE FROM ClientOrder
OUTPUT
      deleted.OrderID AS OrderID,
      deleted.Test AS OldTest,
      deleted.Test AS NewTest,
      'DELETE' as DMLPerformed
WHERE ClientOrder.Test='SVOC Stars'
) t
GO


--let us see what we have in both tables now.
select * from ClientOrder
select * from AuditOfOrder
Go

For example You can read Click1

For example You can read Click2

Thanks and Good day

Yogesh

In SQL Server 2005 and 2008, 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.

OUTPUT clause has accesses to inserted and deleted tables (virtual tables,magic table) just like triggers. 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.

Syntaxt

<OUTPUT_CLAUSE> ::=
{
    [ OUTPUT <dml_select_list> INTO { @table_variable | output_table } [ ( column_list ) ] ]
    [ OUTPUT <dml_select_list> ]
}
<dml_select_list> ::=
{ <column_name> | scalar_expression } [ [AS] column_alias_identifier ]
    [ ,...n ]

<column_name> ::=
{ DELETED | INSERTED | from_table_name } . { * | column_name }
    | $action

The OUTPUT clause and the OUTPUT INTO { @tablevariable | outputtable } clause can be defined in a single INSERT, UPDATE, DELETE, or MERGE statement.

The OUTPUT clause may be useful to retrieve the value of identity or computed columns after an INSERT or UPDATE operation.

When a computed column is included in the , the corresponding column in the output table or table variable is not a computed column. The values in the new column are the values that were computed at the time the statement was executed.

There is no guarantee that the order in which the changes are applied to the table and the order in which the rows are inserted into the output table or table variable will correspond.

If parameters or variables are modified as part of an UPDATE statement, the OUTPUT clause always returns the value of the parameter or variable as it was before the statement executed instead of the modified value.

You can use OUTPUT with an UPDATE or DELETE statement positioned on a cursor that uses WHERE CURRENT OF syntax.

Example

 CREATE TABLE t (id INT)
   GO

   INSERT INTO t VALUES(1)
   INSERT INTO t VALUES(2)
   INSERT INTO t VALUES(3)
   INSERT INTO t VALUES(4)
   GO

   -- this displays what was deleted
   DELETE t
          OUTPUT deleted.id AS 'deleted';

   -- this displays what is inserted
   INSERT INTO t
       OUTPUT inserted.id AS 'inserted'
   VALUES(1)

The OUTPUT clause is not supported in the following statements:

1. DML statements that reference local partitioned views, distributed partitioned views, or remote tables.

2. INSERT statements that contain an EXECUTE statement.

3. Full-text predicates are not allowed in the OUTPUT clause when the database compatibility level is set to 100.

4. The OUTPUT INTO clause cannot be used to insert into a view, or rowset function.

5. A user-defined function cannot be created if it contains an OUTPUT INTO clause that has a table as its target.

To prevent nondeterministic behavior, the OUTPUT clause cannot contain the following references:

1. Subqueries or user-defined functions that perform user or system data access, or are assumed to perform such access. User-defined functions are assumed to perform data access if they are not schema-bound.

2. A column from a view or inline table-valued function when that column is defined by one of the following methods:
   . A subquery.
   . A user-defined function that performs user or system data access, or is assumed to perform such access.
   . A computed column that contains a user-defined function that performs user or system data access in its definition.

3. When SQL Server detects such a column in the OUTPUT clause, error 4186 is raised. For more information, see MSSQLSERVER_4186.

Inserting Data Returned From an OUTPUT Clause Into a Table

The whole operation is atomic. Either both the INSERT statement and the nested DML statement that contains the OUTPUT clause execute, or the whole statement fails.

1. The following restrictions apply to the target of the outer INSERT statement:

   . The target cannot be a remote table, view, or common table expression.

   . The target cannot have a FOREIGN KEY constraint, or be referenced by a FOREIGN KEY constraint.

   . Triggers cannot be defined on the target.

   . The target cannot participate in merge replication or updatable subscriptions for transactional replication.

2. The following restrictions apply to the nested DML statement:

   . The target cannot be a remote table or partitioned view.

   . The source itself cannot contain a clause.

3. The OUTPUT INTO clause is not supported in INSERT statements that contain a clause.

4. @@ROWCOUNT returns the rows inserted only by the outer INSERT statement.

5. @@IDENTITY, SCOPEIDENTITY, and IDENTCURRENT return identity values generated only by the nested DML statement, and not those generated by the outer INSERT statement.

6. Query notifications treat the statement as a single entity, and the type of any message that is created will be the type of the nested DML, even if the significant change is from the outer INSERT statement itself.

7. In the clause, the SELECT and WHERE clauses cannot include subqueries, aggregate functions, ranking functions, full-text predicates, user-defined functions that perform data access, or the TEXTPTR function.

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

http://msdn.microsoft.com/en-us/library/ms177564.aspx

It's funny, but i didn't know about OUTPUT clause before this quiz and was using insert, selcet, but now i know :)

OUTPUT is usefull when you want to know what changes you just made to a table. For example if you DELETE something from a table you can use OUTPUT to see the rows that were deleted from the table.

You can use OUTPUT with following statements: INSERT, UPDATE, DELETE, and MERGE

Bacuase the question was about INSERT and SELECT, I'm going to an INSERT and SELECT example

--creating our main table
CREATE TABLE dbo.TEST-Table1
(
    ID INT,
    FName VARCHAR(32),
    LName VARCHAR(32)
)
Go

--insering values to our main table
INSERT INTO dbo.TEST-Table1 VALUES 
     (1, 'Fred', 'Alexander')
    ,(2, 'Tom', 'Mann')
    ,(3, 'Cindy', 'Peer')
    ,(4, 'Mike', 'Anderson');
GO

--creating our audit table
CREATE TABLE dbo.TEST-InsertedItems
(
    ID INT,
    FName VARCHAR(32),
    LName VARCHAR(32)
)
GO

--inseting new values to our main table
INSERT INTO TEST-Table1(ID, FName ,LName)
--using OUTPUT clause to insert new values to audit table
OUTPUT Inserted.* 
INTO TEST-InsertedItems
VALUES (5, 'Jeff', 'Marr') 


select * from dbo.TEST-Table1

ID  FName	LNam
1   Fred	Alexander
2   Tom	Mann
3   Cindy	Peer
4   Mike	Anderson
5   Jeff	Marr

select * from dbo.TEST-InsertedItems

ID  FName	LName
5   Jeff	Marr

As you can see dbo.TEST-InsertedItems has the insered row.

OUTPUT clause is useful for audit-trail, so you can keep track of changes.

Visit http://msdn.microsoft.com/en-us/library/ms177564.aspx for more information.

OUTPUT clause will return rows to the client even if the statement encounters errors and is rolled back.

Normally used in INSERT,DELETE,UPDATE and MERGE.

OUTPUT clause has accesses to inserted and deleted tables (virtual tables) just like triggers. OUTPUT clause can be used to return values to client clause. OUTPUT clause can be used with INSERT, UPDATE, or DELETE to identify the actual rows affected by these statements.

OUTPUT clause can generate table variable, a permanent table, or temporary table. Even though, @@Identity will still work in SQL Server 2005, however I find OUTPUT clause very easy and powerful to use. Let us understand OUTPUT clause using example.
————————————————————————————————————————
—-Example 1 : OUTPUT clause into Table with INSERT statement

USE AdventureWorks;
GO
--------Creating the table which will store permanent table
CREATE TABLE TestTable (ID INT, TEXTVal VARCHAR(100))
----Creating temp table to store ovalues of 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 TestTable (ID, TEXTVal)
OUTPUT Inserted.ID, Inserted.TEXTVal INTO @TmpTable
VALUES (1,'FirstVal')
INSERT TestTable (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
----Clean up time
DROP TABLE TestTable
GO
ResultSet 1:
ID TextVal
——————— ————————
1 FirstVal
2 SecondVal

ID TextVal
——————— ———————
1 FirstVal
2 SecondVal
————————————————————————————————————————
—-Example 2 : OUTPUT clause with INSERT statement
————————————————————————————————————————
USE AdventureWorks;
GO
----Creating the table which will store permanent table
CREATE TABLE TestTable (ID INT, TEXTVal VARCHAR(100))
----Insert values in real table as well use OUTPUT clause to insert
----values in the temp table.
INSERT TestTable (ID, TEXTVal)
OUTPUT Inserted.ID, Inserted.TEXTVal
VALUES (1,'FirstVal')
INSERT TestTable (ID, TEXTVal)
OUTPUT Inserted.ID, Inserted.TEXTVal
VALUES (2,'SecondVal')
----Clean up time
DROP TABLE TestTable
GO
ResultSet 2:
ID TextVal
——————— ———————
1 FirstVal

(1 row(s) affected)

ID TextVal
——————— ———————
2 SecondVal
————————————————————————————————————————
—-Example 3 : OUTPUT clause into Table with UPDATE statement
————————————————————————————————————————
USE AdventureWorks;
GO
----Creating the table which will store permanent table
CREATE TABLE TestTable (ID INT, TEXTVal VARCHAR(100))
----Creating temp table to store ovalues of OUTPUT clause
DECLARE @TmpTable TABLE (ID_New INT, TEXTVal_New VARCHAR(100),ID_Old INT, TEXTVal_Old VARCHAR(100))
----Insert values in real table
INSERT TestTable (ID, TEXTVal)
VALUES (1,'FirstVal')
INSERT TestTable (ID, TEXTVal)
VALUES (2,'SecondVal')
----Update the table and insert values in temp table using Output clause
UPDATE TestTable
SET TEXTVal = 'NewValue'
OUTPUT Inserted.ID, Inserted.TEXTVal, Deleted.ID, Deleted.TEXTVal INTO @TmpTable
WHERE ID IN (1,2)
----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
----Clean up time
DROP TABLE TestTable
GO
ResultSet 3:
ID_New TextVal_New ID_Old TextVal_Old
——————— ——————— ——————— ———————
1 NewValue 1 FirstVal
2 NewValue 2 SecondVal

ID TextVal
——————— ———————
1 NewValue
2 NewValue
————————————————————————————————————————
—-Example 4 : OUTPUT clause into Table with DELETE statement
————————————————————————————————————————
USE AdventureWorks;
GO

--Creating the table which will store permanent table
CREATE TABLE TestTable (ID INT, TEXTVal VARCHAR(100))
----Creating temp table to store ovalues of OUTPUT clause
DECLARE @TmpTable TABLE (ID INT, TEXTVal VARCHAR(100))
----Insert values in real table
INSERT TestTable (ID, TEXTVal)
VALUES (1,'FirstVal')
INSERT TestTable (ID, TEXTVal)
VALUES (2,'SecondVal')
----Update the table and insert values in temp table using Output clause
DELETE
FROM TestTable
OUTPUT Deleted.ID, Deleted.TEXTVal INTO @TmpTable
WHERE ID IN (1,2)
----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
----Clean up time
DROP TABLE TestTable
GO
ResultSet 4:
ID TextVal
——————— ———————
1 FirstVal
2 SecondVal

ID TextVal
——————— ———————
If we run all the above four example, you will find that OUTPUT clause is very useful. 

The OUTPUT clause may also be useful to retrieve the value of identity or computed columns after an INSERT or UPDATE operation.

When a computed column is included in the <dml_select_list>, the corresponding column in the output table or table variable is not a computed column. The values in the new column are the values that were computed at the time the statement was executed.

There is no guarantee that the order in which the changes are applied to the table and the order in which the rows are inserted into the output table or table variable will correspond.

If parameters or variables are modified as part of an UPDATE statement, the OUTPUT clause always returns the value of the parameter or variable as it was before the statement executed instead of the modified value.

You can use OUTPUT with an UPDATE or DELETE statement positioned on a cursor that uses WHERE CURRENT OF syntax.

The OUTPUT clause is not supported in the following statements:

    DML statements that reference local partitioned views, distributed partitioned views, or remote tables.

    INSERT statements that contain an EXECUTE statement.

    Full-text predicates are not allowed in the OUTPUT clause when the database compatibility level is set to 100.

    The OUTPUT INTO clause cannot be used to insert into a view, or rowset function.

    A user-defined function cannot be created if it contains an OUTPUT INTO clause that has a table as its target.

To prevent nondeterministic behavior, the OUTPUT clause cannot contain the following references:

    Subqueries or user-defined functions that perform user or system data access, or are assumed to perform such access. User-defined functions are assumed to perform data access if they are not schema-bound.

    A column from a view or inline table-valued function when that column is defined by one of the following methods:

        A subquery.

        A user-defined function that performs user or system data access, or is assumed to perform such access.

        A computed column that contains a user-defined function that performs user or system data access in its definition.

OUTPUT clause returns information from, or expressions based on, each row affected by an INSERT, UPDATE, DELETE, or MERGE statement. These results can be returned to the processing application for use in such things as confirmation messages, archiving, and other such application requirements. The results can also be inserted into a table or table variable. Additionally, you can capture the results of an OUTPUT clause in a nested INSERT, UPDATE, DELETE, or MERGE statement, and insert those results into a target table or view.

OUTPUT clause can be used in

1. DELETE
2. INSERT
3. UPDATE
4. MERGE

We can use OUTPUT clause as follows:

DECLARE @Records TABLE
(  Column1 UNIQUEIDENTIFIER )

DELETE  FROM Table1
OUTPUT  deleted.Column1
  INTO @Records ( Column1 )
WHERE Col2 = 11

SELECT COUNT(*) AS AffectedRecordCount FROM    @Records

The OUTPUT clause is not supported in the following statements:

• DML statements that reference local partitioned views, distributed partitioned views, or remote tables.
• INSERT statements that contain an EXECUTE statement.
• Full-text predicates are not allowed in the OUTPUT clause when the database compatibility level is set to 100.
• The OUTPUT INTO clause cannot be used to insert into a view, or rowset function.
• A user-defined function cannot be created if it contains an OUTPUT INTO clause that has a table as its target.

OUTPUT CLAUSE

Returns information from, or expressions based on, each row affected by an INSERT, UPDATE, DELETE, or MERGE statement. These results can be returned to the processing application for use in such things as confirmation messages, archiving, and other such application requirements. The results can also be inserted into a table or table variable. Additionally, you can capture the results of an OUTPUT clause in a nested INSERT, UPDATE, DELETE, or MERGE statement, and insert those results into a target table or view.

OUTPUT clause can generate table variable, a permanent table, or temporary table.

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.

Create Table MyTable (ID int, name varchar(100),createdAt datetime)

-- declare @InsertOutput2 table variable 
DECLARE @InsertOutput2 table
(
  ID int,
  name nvarchar(50),
  CreatedAt datetime
);

 -- insert new row into table in your database
INSERT INTO MyTable
OUTPUT 
    INSERTED.ID, 
    INSERTED.Name, 
    INSERTED.CreatedAt
  INTO @InsertOutput2
VALUES(111, 'Some text', GETDATE());


-- view inserted row in table
SELECT * FROM MyTable;
-- view output row in @InsertOutput2 variable
SELECT * FROM @InsertOutput2;

drop table MyTable

Using an OUTPUT Clause in an UPDATE Statement

Create Table MyTable (ID int, name varchar(100),createdAt datetime)

-- declare @InsertOutput2 table variable 
DECLARE @UpdateOutput1 table
(
  ID int,  
  name nvarchar(50),
  Oldname nvarchar(50),
  CreatedAt datetime,
  UpdatedAt datetime
);

 -- insert new row into table in your database
INSERT INTO MyTable
VALUES(111, 'Some text', GETDATE());


UPDATE MyTable
SET   
  name = 'Changed...'
OUTPUT    
    INSERTED.ID,
    INSERTED.Name,
    DELETED.Name,
    DELETED.CreatedAt,
    getdate()
  INTO @UpdateOutput1
WHERE Name = 'Some text';



-- view inserted row in table
SELECT * FROM MyTable;
-- view output row in @InsertOutput2 variable
SELECT * FROM @UpdateOutput1;

drop table MyTable

In the same manner you can use it with DELETE statement. It uises the same INSERTED and DELETED memory tables.

Using an OUTPUT Clause in an MERGE Statement

When working in SQL Server 2008, you can add an OUTPUT clause to a MERGE statement. The process is similar to adding the clause to an UPDATE statement; you use both the INSERTED and DELETED column prefixes.

Let’s look at an example that demonstrates how this work. First, however, we must create a second table to support the MERGE statement. The following script creates the Book2 table and populates it with two rows:

CREATE TABLE dbo.Books2
(
  BookID int NOT NULL PRIMARY KEY,
  BookTitle nvarchar(50) NOT NULL,
  ModifiedDate datetime NOT NULL
);


CREATE TABLE dbo.Books
(
  BookID int NOT NULL PRIMARY KEY,
  BookTitle nvarchar(50) NOT NULL,
  ModifiedDate datetime NOT NULL
);



INSERT INTO Books2
     VALUES(101, '100 Years of Solitude', GETDATE());



INSERT INTO Books2
     VALUES(102, 'Pride & Prejudice', GETDATE());

--Once we’ve created the Books2 table, we can try a MERGE statement. In the following example, I declare the @MergeOutput1 variable, merge data from the Books table into the Books2 table, and view the results:
-- declare @MergeOutput1 table variable

DECLARE @MergeOutput1 table
(
  ActionType nvarchar(10),
  BookID int,
  OldBookTitle nvarchar(50),
  NewBookTitle nvarchar(50),
  ModifiedDate datetime
);


INSERT INTO Books
     VALUES(101, 'The Great Gatsby', GETDATE());


-- use MERGE statement to perform update on Book2
MERGE Books2 AS b2 
USING Books AS b1
ON (b2.BookID = b1.BookID)
WHEN MATCHED
THEN UPDATE 
SET b2.BookTitle = b1.BookTitle
OUTPUT
    $action,
    INSERTED.BookID,
    DELETED.BookTitle,
    INSERTED.BookTitle,
    INSERTED.ModifiedDate
  INTO @MergeOutput1;


-- view Books table
SELECT * FROM Books; 

-- view updated rows in Books2 table
SELECT * FROM Books2; 

-- view output rows in @MergeOutput1 variable
SELECT * FROM @MergeOutput1;



drop table Books
drop table Books2

$action Gives us the type of action that is performed by Merge statement per record.

OUTPUT Cluase

When we try to execute any DML statements, such as inserting a row, DML statements do not produce any results (apart from showing messages). OUTPUT clause helps to get it. 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.

In fact, all deleted or inserted information gets stored in a virtual table. By using a simple SELECT statement, we can retrieve all of the information available in the virtual table and display it to the screen. You can use the OUTPUT clause together with the inserted and deleted virtual tables, as you might use a trigger. But please be aware that the OUTPUT clause must be used with an INTO expression to fill a table.

OUTPUT clause with INSERT statement

single INSERT statement Following example, insert a single row into the table and display the result on the screen.

Use Northwind

Declare @ins as table(InsRegionID int,InsRegionDescription nchar(50))

Insert Region 
Output inserted.regionid,inserted.regiondescription into @ins
values(5,'NorthEast')

--Display Newly Inserted Value will be displayed
Select * from @ins

--Displays all values along with newly Inserted value will be displayed

Select * from Region

multiple INSERT statements

Use Northwind

Declare @ins as table(InsRegionID int,InsRegionDescription nchar(50))

Insert Region(RegionID,RegionDescription) 
Output inserted.Regionid,inserted.RegionDescription into @ins
values(7,'NorthWest')

Insert Region(RegionID,RegionDescription) 
Output inserted.Regionid,inserted.RegionDescription into @ins
values(8,'SouthEast')

Insert Region(RegionID,RegionDescription) 
Output inserted.Regionid,inserted.RegionDescription into @ins
values(9,'NorthEast')


Insert Region 
Output inserted.Regionid,inserted.RegionDescription into @ins
values(10,'SouthWest')

--Display Newly Inserted Value will be displayed
Select * from @ins

--Displays all values along with newly Inserted values will be displayed

Select * from Region

OUTPUT clause with single DELETE statement

Use Northwind

Declare @del as table(delRegionID int,delRegionDescription nchar(50))

Delete  region
Output deleted.regionid,deleted.regiondescription
into @del
Where regionid=7

--Display Deleted Value will be displayed

Select * from @del

OUTPUT clause with single UPDATE statement

Use Northwind

Declare @upd as table(UpRegionID int,UpRegionDescription nchar(50),UpNewRegionDescription nchar(50))

Update region
Set RegionDescription='NorthernWest'
Output inserted.regionid,deleted.regiondescription,inserted.
regiondescription
into @upd
Where regionid=6

--Display Newly Updated Value will be displayed
Select * from @upd

--Displays all values along with newly Updated value will be displayed

Select * from Region

OUTPUT Clause in a MERGE Statement

The following script creates the Book2 table and populates it with two rows:

CREATE TABLE dbo.Books2(
  BookID int NOT NULL PRIMARY KEY,
  BookTitle nvarchar(50) NOT NULL,
  ModifiedDate datetime NOT NULL
);

INSERT INTO Books2
VALUES(101, '100 Years of Solitude', GETDATE());

INSERT INTO Books2
VALUES(102, 'Pride & Prejudice', GETDATE());

Once we’ve created the Books2 table, we can try a MERGE statement. In the following example, I declare the @MergeOutput1 variable, merge data from the Books table into the Books2 table, and view the results:

-

- declare @MergeOutput1 table variable
DECLARE @MergeOutput1 table
(
  ActionType nvarchar(10),
  BookID int,
  OldBookTitle nvarchar(50),
  NewBookTitle nvarchar(50),
  ModifiedDate datetime
);

-- use MERGE statement to perform update on Book2
MERGE Books2 AS b2 
USING Books AS b1
ON (b2.BookID = b1.BookID)
WHEN MATCHED
THEN UPDATE 
SET b2.BookTitle = b1.BookTitle
OUTPUT
    $action,
    INSERTED.BookID,
    DELETED.BookTitle,
    INSERTED.BookTitle,
    INSERTED.ModifiedDate
  INTO @MergeOutput1;

-- view Books table
SELECT * FROM Books;

-- view updated rows in Books2 table
SELECT * FROM Books2;

-- view output rows in @MergeOutput1 variable
SELECT * FROM @MergeOutput1;

Here we used $action variable. $action is a built-in parameter returns one of three nvarchar(10) values—INSERT, UPDATE, or DELETE—and is available only to the MERGE statement. The value returned depends on the action performed on the row.

Points To Remember

When a computed column is included in the dmlselectlist, the corresponding column in the output table or table variable is not a computed column. The values in the new column are the values that were computed at the time the statement was executed. There is no guarantee that the order in which the changes are applied to the table and the order in which the rows are inserted into the output table or table variable will correspond. If parameters or variables are modified as part of an UPDATE statement, the OUTPUT clause always returns the value of the parameter or variable as it was before the statement executed instead of the modified value.

Limitations

I if the DML statement fails, for example, due to a constraint violation, the OUTPUT clause would still return the rows that would have been inserted or deleted. Therefore, we should never rely on the OUTPUT statement to indicate that the statement was successful. Your database application should check for errors from DML statements. If the application encounters an error, the OUTPUT results should not be used.

The OUTPUT clause is not supported in the following statements:

• DML statements that reference local partitioned views, distributed partitioned views, or remote tables.
• INSERT statements that contain an EXECUTE statement.
• Full-text predicates are not allowed in the OUTPUT clause when the database compatibility level is set to 100.
• The OUTPUT INTO clause cannot be used to insert into a view, or rowset function.
• A user-defined function cannot be created if it contains an OUTPUT INTO clause that has a table as its target.

To prevent nondeterministic behavior, the OUTPUT clause cannot contain the following references:

• Subqueries or user-defined functions that perform user or system data access, or are assumed to perform such access. User-defined functions are assumed to perform data access if they are not schema-bound.

• A column from a view or inline table-valued function when that column is defined by one of the following methods:

  1.A subquery.

  2.A user-defined function that performs user or system data access, or is assumed to perform such access.

  3.A computed column that contains a user-defined function that performs user or system data access in its definition. When SQL Server detects such a column in the OUTPUT clause, error 4186 is raised.

References

http://www.aspfree.com/c/a/MS-SQL-Server/Wonders-of-the-OUTPUT-Clause-in-SQL-Server-2005/

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

http://msdn.microsoft.com/en-us/library/ms177564.aspx