How can I get information about the inserted or deleted rows from a table in SQL SERVER?

home / software / tips and tricks / How can I get information about the inserted or deleted rows from a table in SQL SERVER?

How can I get information about the inserted or deleted rows from a table in SQL SERVER?

Category: SQL Server
view more software Tips and Tricks

Updated: 04/22/2011 14:04 PM
Author: Shiju Mathews

Status: Resolved.

SQL Server 2005 introduced OUTPUT Clause (Transact-SQL) that returns information from, or expressions based on, each row affected by an INSERT, UPDATE, or DELETE statement. These results can be returned to the processing application for use in such things as confirmation messages, archiving, and other such application requirements. Alternatively, results can be inserted into a table, table variable or temporary table.

This can be use conjunction with DELETE ,INSERT ,UPDATE clause.

CREATE TABLE OutPutTable (OutPutID INT Identity(1,1),OutPutTEXT VARCHAR(100))
GO
--Insert
Insert Into OutPutTable (OutPutTEXT)
OUTPUT Inserted.OutPutID VALUES ('FirstVal')
Insert Into OutPutTable (OutPutTEXT)
OUTPUT Inserted.OutPutID VALUES ('Second Val')
--Create A temp table to recieve rows
DECLARE @TmpTable TABLE (IDVal INT, TEXTVal VARCHAR(100))
Insert Into OutPutTable (OutPutTEXT)
OUTPUT Inserted.OutPutID, Inserted.OutPutTEXT into @TmpTable
VALUES ('Third Val')
SELECT * from @TmpTable
DELETE from @TmpTable
Insert Into OutPutTable (OutPutTEXT)
OUTPUT Inserted.* into @TmpTable
VALUES ('Fourth Val')
SELECT * from @TmpTable
DELETE from @TmpTable
Insert Into OutPutTable (OutPutTEXT)
OUTPUT Inserted.OutPutID, Inserted.OutPutTEXT
VALUES ('Fifth Val')
--Delete DELETE FROM OutPutTable
OUTPUT DELETED.OutPutID
where OutPutID = 5
DELETE FROM OutPutTable
OUTPUT DELETED.OutPutID, DELETED.OutPutTEXT
where OutPutID = 4
DELETE from @TmpTable
DELETE FROM OutPutTable
OUTPUT DELETED.OutPutID, DELETED.OutPutTEXT into @TmpTable
where OutPutID = 3
SELECT * from @TmpTable

For more information visit technet.microsoft.com

Indian Resources by Category

Indian Grocery Stores

Indian Jewelry Stores

Indian Hindu Temple

alt="Add your business to our listing Free"

Software Resources

Tips and Tricks

Interview Questions for C#

Interview Questions for ASP.Net

Interview Questions for SQL Server