Loading ...

SQL: How to get id of recently deleted rows | CodeAsp.Net

SQL: How to get id of recently deleted rows

 /5
0 (0votes)

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.aspx
Output: 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 ,
      [Name] VARCHAR(20)
    )

-- INSERT FEW RECORDS
INSERT  INTO @Test
        SELECT  'Abc'
        UNION ALL
        SELECT  'Def'
        UNION ALL
        SELECT  'Ghi'

--CREATE A TEMP TABLE FOR HOLDING DELETED ID'S
DECLARE @TempIDS TABLE ( [RowID] INT )

DELETE @Test
OUTPUT DELETED.id INTO @TempIDS
WHERE ID>1


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.

Comments (no comments yet)

Top Posts