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 @TABOUTPUT
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.