September 29, 2008 at 6:25 am
Hi All,
In one of the stored procedure, I am inserting a record into table t1 which is having identity column as PK. I used Ident_Current('t1') to get the identity value.
It was working fine in production, but recently I found Ident_Current is not giving expected result.
Please guide me which one will give last inserted identity value among Ident_Current, Scope_Identity and @@Identity.
September 29, 2008 at 7:44 am
if you want to get the value just inserted into the table after your INSERT, and you know that the INSERT in your stored procedure only adds 1 row to the table then I'd say use SCOPE_IDENTITY() function immediately after your INSERT statement. IDENT_CURRENT() will not always give you what you expect because there could be multiple people adding records to the same table, so you wouldn't necessarily get the identity value you added to the table. The problem with @@Identity is that it can return the identity value from a different table than what you're expecting. Books Online will tell you that SCOPE_IDENTITY() limmits what it returns to the same stored procedure, trigger, function, or batch.
If your INSERT statement can add multiple rows to the table, then you can use the OUTPUT clause of the INSERT statement to put the entire list into a table variable.
September 29, 2008 at 7:52 am
Chris has it correct, scope_identity is what you want.
September 29, 2008 at 3:56 pm
Chris Harshman (9/29/2008)
If your INSERT statement can add multiple rows to the table, then you can use the OUTPUT clause of the INSERT statement to put the entire list into a table variable.
Not in SQL2000
_____________
Code for TallyGenerator
September 29, 2008 at 9:39 pm
Thanks for the reply. I will go with scope_identity.
November 5, 2008 at 3:16 pm
I am new to sql server and I need your suggestions:
which one is the best for PK generation(@@IDENTITY, SCOPE_IDENTITY, and IDENT_CURRENT ). If i have transactions like this.
begin tran
Insert into customer(id,fname,lname) (4,'sameer','dutts')
@cid=SCOPE_IDENTITY --cid will be 4
Insert into address (adrsid,customerid,'addressvalue')(1,4,'dutts')
end tran
for such transactions what identity variable we should use.
Does begin tran end tran makes the current scope to return the correct identity value.Pls let me know asap.
November 5, 2008 at 9:56 pm
Hello Man,:w00t:
Just imagine there are two tables named Tbl1, Tbl2 those contains identity field, right. Think, here you have a trigger stuff that will execute when ever an insert operation happened on Tbl2, that time from mach table(Inserted) ‘s news value will insert into Tbl1 got!!
Come back to your doubt,
If you are using Scope_Identity , by the name itself we know that is limited to its current scope and in our case that will return Tbl2’s last identity value, but actually that last identity updating happened on Tbl1 by the trigger
This identity value holds @@Identity System Variable.
@@Identity - his is not limited to the current scope
Scope_Identity – limited to the current scope
I think you got his light things.
Anoop.Sakthidharan
June 5, 2013 at 11:34 am
Assume that there are a lot of people (e.g. 1,000+) are inserting records simultaneously into the database, will using SCOPE_IDENTITY (or any identity) right after the insertion return the correct PK? Would there be a chance that one personal will get a PK generated by another person since we have so many users inserting data into the database.
Chai Xiong
Hosted Service Manager
Zelle LLP
June 5, 2013 at 1:09 pm
chaijxiong (6/5/2013)
Assume that there are a lot of people (e.g. 1,000+) are inserting records simultaneously into the database, will using SCOPE_IDENTITY (or any identity) right after the insertion return the correct PK? Would there be a chance that one personal will get a PK generated by another person since we have so many users inserting data into the database.
Note, this thread is 5 years old.
You should check out the online documentation and the previous responses in this thread. SCOPE_IDENTITY is limited to the current context.
Check out this link. It explains the possibilities very clearly and concisely.
http://blog.sqlauthority.com/2007/03/25/sql-server-identity-vs-scope_identity-vs-ident_current-retrieve-last-inserted-identity-of-record/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply