How we return identity value from sql table

Sanwar Ranwa

8y1.1k

How we return identity value from sql table if there are 20 simultaneously request to insert record in db ?

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

Ravi Kiran Chanduri8y ago
Hello Sanwar ,..

To we have a predefined function called Scope_Identity() to get the Recently generated Identity column value .
Create Table #Tab (id int identity(1,1) , age int)-- Your Actual Table

insert into #tab(age) values(25)
Select Scope_Identity()-- to get the recent identity column value
You are saying there will be lot of simultaneous requests .. better to use following technique.
Create Table #Tab (id int identity(1,1) , age int)-- Your Actual Table

declare @recentID table (recentID int); -- recently generated Id will be resided in it

insert into #tab(age)
output INSERTED.id into @recentID values(25)

Select * FROM  @recentID
0
Bhavesh Jadav8y ago
thanks Shahriar Nikkhah,
for like my solution, i keep it more in details next time.
0
Shahriar Nikkhah8y ago
Hi
The second answer from Bhavesh is the best becasue it will give you the identity number from the current Transaction , i use that alot in replicating SCDType2 In SSIS

Good luck
Nik
+2
Bhavesh Jadav8y ago
Hello Sanwar,

You should use

1) SCOPE_IDENTITY() to get return value from sql table
INSERT INTO table (name) VALUES('abc');
SELECT SCOPE_IDENTITY()
It returns last identity value for any table in the current session and the current scope

2) You can get last inserted value as below
INSERT INTO table (name) OUTPUT INSERTED.YourIdentity VALUES('abc');
It returns last identity value.

3) Also you can try IDENT_CURRENT
INSERT INTO table (name) VALUES('abc');
SELECT IDENT_CURRENT('table_name')
It returns the last identity value generated for a specific table in any session and any scope

Thanks.
+1
Piyush Pansuriya8y ago
Hello,
DECLARE @ID INT
INSERT INTO (Column1, Column2, Column3, ...)
VALUES  (Value1, Value2, Value3, ...)
SET @ID = IDENT_CURRENT('TABLE_NAME')
IDENT_CURRENT return the value of Identity Column.
+1
Midhun Tp8y ago
Hi,

Please go through below thread -

https://stackoverflow.com/questions/31238231/returning-scope-identity-from-2-insert-statements-simultaneously-in-sql-server
0