In MS SQL I've seen two ways to handle returning the last inserted primary
key:
INSERT INTO t_doctors (#ColNames#)
VALUES (#preserveSingleQuotes(ColValues)#);
SELECT SCOPE_IDENTITY() AS newpkey;
This will return the last inserted primary key for the current scope
(session), so there is no concern of race conditions.
INSERT INTO t_doctors (#ColNames#)
VALUES (#preserveSingleQuotes(ColValues)#);
SELECT @@identity AS newpkey;
While this will return the last inserted primary key across scopes
(sessions). Using @@identity will leave you open to potential problems with
race conditions.
This is from information I've read in the BOL as well as been informed from
our DBA. I can say that when using SCOPE_IDENTITY() I've never found any
errors under load, but if anyone else has any input I'd love to hear it.
Rich Kroll
Application Developer
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:236877
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
