Using OUTPUT Clause in DML statements.

The OUTPUT clause returns a copy of the data that you’ve inserted into or deleted from your tables. You can return that data to a temporary or permanent table, a table variable, 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.

The OUTPUT clause takes two basic forms:

i. OUTPUT (want to return the data to the calling application)

ii. OUTPUT INTO (to return the data to a table or a table variable.)

In the below example I just created a table called ‘Test1’ and I used a table variable called ‘@TempTest1’ to show the computed records.

OUTPUT Clause in an INSERT Statement:

We can use OUTPUT clause while inserting data into a table, to return a copy of the data that’s been inserted into the table.

INSERT INTO dbo.Test1

OUTPUT INSERTED.sno

                    ,INSERTED.ename

                    ,INSERTED.eloc

INTO     @TempTest1

VALUES (1,’peter’,’MI’)

OUTPUT Clause in an UPDATE Statement:

I have just inserted a record into ‘Test1’ table, now I want to update the ename value of that record(from ‘Peter’ to ‘John Peter’). We can get both the previous value and updated value if we use OUTPUT clause while updating the ename value.

UPDATE     dbo.Test1

SET ename=’John Peter’

OUTPUT  INSERTED.sno

                     ,DELETED.ename

                     ,INSERTED.ename

                    ,INSERTED.eloc

INTO @TempTest_updation

WHERE sno=1

OUTPUT Clause in an DELETE Statement:

We can use OUTPUT clause in a DELETE statement to archive the records from a table into Archive table. The OUTPUT clause will insert deleted records in archive table.

DELETE dbo.Test1

OUTPUT DELETED.sno

,DELETED.ename

,DELETED.eloc

INTO @TempTest_Deletion

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.

The OUTPUT clause cannot contain the following references:

1. · Sub queries 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 sub query.
• 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.