SQL SERVER – 2005 – OUTPUT Clause

         In SQl Server OUTPUT Clause is just like a Triggers, Expressions based on, each row affected by an INSERT, UPDATE, DELETE, or MERGE statement.

INSERT statement



CREATE TABLE TestTable (ID INT, TEXTVal VARCHAR(100))

DECLARE @TmpTable TABLE (ID INT, TEXTVal VARCHAR(100))

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')

SELECT * FROM @TmpTable
SELECT * FROM TestTable

UPDATE STATEMENT 








CREATE TABLE TestTable (ID INT, TEXTVal VARCHAR(100))


CREATE TABLE BAK_TestTable (Old_ID INT, Old_TEXTVal VARCHAR(100),New_ID INT, New_TEXTVal VARCHAR(100),Date DATETIME)

update TestTable set id=6 ,textval='Dinesh'
output deleted.ID,deleted.textval,inserted.id,inserted.textval,getdate() into BAK_TestTable
where id=2 







DELETE STATEMENT 

DELETE FROM  TestTable 

OUTPUT Deleted.* 

Where id=6

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