Loading ...

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

SQL: How to get id of recently inserted rows

 /5
0 (0votes)

I have seen this question many times on forums on how to get recently inserted 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)
    )


DECLARE @TempIDS TABLE ( [RowID] INT )

INSERT  @Test
OUTPUT  Inserted.ID
        INTO @TempIDS
        SELECT  'Abc'
        UNION ALL
        SELECT  'Def'
        UNION ALL
        SELECT  'Ghi'
        
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