TOP 1 WITH TIES

SQL Server 2005 introduces a new feature for retrieving rows affected by an INSERT, UPDATE or Delete statement. It is a common requirement to get the affected rows back for further processing, auditing or simply return them back to the client.

Those who had worked with triggers are familiar with the special inserted and deleted tables. The new OUTPUT clause also work in the same manner. You can use the OUTPUT keyword to return information about the results of a Transact-SQL statement into a table variable, temporary table, permanent table or just return it back to the client.

Using the OUTPUT Clause with INSERT


CREATE Table #OutputTest(someid int IDENTITY(1,1), sometext varchar(100))
--Table variable to collect changes

DECLARE @InsertDetails TABLE(someid int,sometext varchar(100))
INSERT INTO #OutputTest(sometext)
OUTPUT Inserted.SomeId, Inserted.someText INTO @InsertDetails
VALUES('Some Text goes Here')

SELECT * FROM @InsertDetails


Here is the result.
(1 row(s) affected)
someid sometext
----------- ----------------------------------
1 Some Text goes Here

(1 row(s) affected)

Note that we are retriving the just inserted IDENTITY value also.

Now lets try to insert a batch of rows.


--Table variable to collect changes
DECLARE @InsertDetails TABLE(someid int,sometext varchar(100))

--Inserting a batch
INSERT INTO #OutputTest(sometext)
OUTPUT Inserted.SomeId, Inserted.someText INTO @InsertDetails
SELECT 'Test 1' UNION
SELECT 'Test 2' UNION
SELECT 'Test 3'

SELECT * FROM @InsertDetails


And here is the OUTPUT.
(3 row(s) affected)
someid sometext
----------- -----------------------------------------------------------------
1 Test 1
2 Test 2
3 Test 3

(3 row(s) affected)

Using the OUTPUT Clause with UPDATE


DECLARE @UpdateDetails TABLE(id int, prevtext VARCHAR(100), newText VARCHAR(100))

UPDATE #OutputTest
SET sometext = 'XYZ'
OUTPUT deleted.someid, deleted.sometext,inserted.sometext INTO @UpdateDetails
WHERE SomeID < 3

SELECT * FROM @UpdateDetails


Here is the output.

(2 row(s) affected)
id prevtext newText
----------- ---------- ----------
1 Test 1 XYZ
2 Test 2 XYZ

(2 row(s) affected)

Note that here we are capturing both the current and previous values of the affected column.

Using the OUTPUT Clause with DELETE

DECLARE @DeleteDetails TABLE(id int, DeletedBy sysname)
DELETE FROM #OutputTest
OUTPUT deleted.someid, SUSER_NAME() INTO @DeleteDetails
WHERE SomeID=3

SELECT * FROM @DeleteDetails


Here is the result

(1 row(s) affected)
id DeletedBy
----------- --------------------------------------------------------------------------------------------------------------------------------
3 DOMAIN\rojipt

(1 row(s) affected)


I believe the DML OUTPUTwill be one of the most favourite feature of T-SQL developers.