Wednesday, July 3, 2013

Get Id of latest inserted Record in Table

Dear Reader,

One of issue we always worried about was how to get id of latest inserted record.
1. One option was to perform select on that table after inserting latest record.
2. Use or ScopeIdentity
3. Get Inserted id using OUTPUT INSERTED

assuming we have a table tblDemoTable (IDField, Field1,Field2,Field3)
Details on different ways to get ids

 After insert new record we can get if it integer identifier by fetching maximum id values

Declare  @InsertedId int
select top 1 @InsertedId  =Max(IDField)
from tblDemoTable

Note: It doesnt work for Uniqueidentifier column.

And we can also select values based on any date sorting or something if we have any identifier to sort it and take latest record.


 SCOPE_IDENTITY() will provide us the last identity value created in a particular session, but it will also limit it to your current scope as well.
 Link for better understanding-1


This is very useful to get list of Id's inserted in current session inside a loop or in batch.
Limitation: It will work in current session only.

Declare @InsertedID as table
                                Id int
                            Declare @ID int
                            Insert into tblDemoTable( Field1,Field2,Field3)
                                                        OUTPUT INSERTED.[IDField] INTO @InsertedID
                                                        ( @Field1,@Field2,@Field3)

Thank you Rohit Rao for providing solution to me.

Happy Living...
Happy Concepts...
Happy Programming...

No comments:

Post a Comment