Microsoft Technologies, ASP.NET & SQL SERVER Tips with Rajat Jaiswal

SQL SERVER Great Feature “Output Clause” :)

Hello friends,
So some time we faced situation like we have to insert huge amount of data and also keep the trace for the id columns. Or like some time we have to delete the record and also need to keep the track record which ID we have deleted. So here usually people use either cursor or while loop just because they are not aware of the new opportunity which Microsoft SQL SERVRER 2005 has and that opportunity is “OUTPUT Clause”.
So let understand this by some simple examples. I took here 2 examples. In first example we will talk about how output clause helpful with Delete operation and in second example we will talk how output clause helpful in Insert operation.
So Example first here:-

Now consider a delete operation where you need to delete records with some specific condition and whatever record you have deleted you need to provide IDS of those deleted item.
Suppose the table name is tblItem then we will write below query
DECLARE @tblItem AS TABLE (id INT IDENTITY,
[name] VARCHAR(100))
DECLARE @tblInsertedId AS TABLE (id INT)INSERT INTO @tblItem ([name]) OUTPUT Inserted.Id INTO @ tblInsertedId
SELECT Right(UserName,1)
FROM dbo.users

SELECT * FROM @tbl tblInsertedId
So when you run above query you will get identity column values corresponding to tblItem table. Which we have inserted in @tblAllIds table with output inserted clause.
In similar way suppose now we want to delete the records from @tblItem and we need to capture all the delete records ids then also this clause will helpful see below example.
DECLARE @tblItem AS TABLE (id INT IDENTITY,
[name] VARCHAR(100))

DECLARE @tblAllDeletedIds AS TABLE( id INT,Name varchar(100))
INSERT INTO @tblItem ([name])
SELECT Right(UserName,1)
FROM dbo.users— Now suppose we want to delete all the item with name start with Y
— and also want to keep the track what are the ids we have deleted then we use output deleted
DELETE tmp
OUTPUT DELETED.ID,DELETED.Name INTO @tblAllDeletedIds
FROM @tblItem tmp WHERE name like ‘y%’

SELECT * FROM @tblAllDeletedIds

In similar way we can use this output clause with update query also.
So it’s a great feature just enjoy.
It helpful in avoiding cursor and while loop so enjoy this feature.
It helped me a lot hopefully it will helpful to you also.