Output Clause in SQL Server 2005

SQL Server 2005 introduced a new TSQL feature OUTPUT clause that allows you to retrieve data affected by insert/update/delete statements easily. The  OUTPUT clause returns the data that you've inserted or deleted from the table within the statement.

Basically OUTPUT clause has the access to magic tables (inserted and deleted) like triggers. 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  Let us take examples to understand how OUTPUT works.

First create a table Employee

CREATE TABLE Employee(
    EmpId VARCHAR(10), EmpName varchar(10), DOJ DATETIME
)

Example 1: Displaying the data just inserted with output clause within the same insert statement.

INSERT INTO Employee
OUTPUT inserted.*
SELECT 'EMP001', 'Sandeep', '01/01/2008' UNION ALL
SELECT 'EMP002', 'Abhay', '06/01/2008'

OUTPUT

In the above example we have inserted two rows in the Employee table and with OUTPUT clause we are displaying the rows we have just inserted from the same insert statement.

Example 2: Insert the data in other table on insertion and deletion of a table

DECLARE @TAB TABLE(
    EmpId VARCHAR(10), EmpName varchar(10), DOJ DATETIME, Action CHAR(1), LogTime DATETIME
)
INSERT INTO Employee
OUTPUT inserted.*, 'I', GETDATE() INTO @TAB
SELECT 'EMP003', 'Ritesh', '09/01/2008'

DELETE Employee
OUTPUT deleted.*, 'D', GETDATE() INTO @TAB
WHERE EmpId = 'EMP003'

SELECT * FROM @TAB

OUTPUT

In the above example first we have inserted and then deleted a row in the Employee table and with OUTPUT clause we have inserted both the inserted and deleted rows in the table variable from the inserted and deleted tables respectively.