I have seen this question many times on forums on how to get recently deleted rows id in SQL. In this blog I will show the same with the help of OUTPUT clause. From MSDN http://msdn.microsoft.com/en-us/library/ms177564.aspxOutput: Returns information from, or expressions based on, each row affected by an INSERT, UPDATE, DELETE, or MERGE statement. These results can be returned to the processing application for use in such things as confirmation messages, archiving, and other such application requirements. The results can also be inserted into a table or table variable. Additionally, you can capture the results of an OUTPUT clause in a nested INSERT, UPDATE, DELETE, or MERGE statement, and insert those results into a target table or view.
Let's come to sample code to show the same:
DECLARE @Test TABLE
[ID] INT IDENTITY ,
-- INSERT FEW RECORDS
INSERT INTO @Test
--CREATE A TEMP TABLE FOR HOLDING DELETED ID'S
DECLARE @TempIDS TABLE ( [RowID] INT )
OUTPUT DELETED.id INTO @TempIDS
SELECT * FROM @TempIDS
Below is the Output:
As you saw from above we got recently inserted row's id.Do let me know your feedback, comments.