How we return identity value from sql table

Question

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

Shahriar Nikkhah · Answer

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

Bhavesh Jadav · Answer

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.

Piyush Pansuriya · Answer

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.

Bhavesh Jadav · Answer

thanks Shahriar Nikkhah, for like my solution, i keep it more in details next time.

Ravi Kiran Chanduri · Answer

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

Midhun Tp · Answer

Hi, Please go through below thread - https://stackoverflow.com/questions/31238231/returning-scope-identity-from-2-insert-statements-simultaneously-in-sql-server

How we return identity value from sql table

Answers (6)