January 7, 2003 at 8:56 am
Good morning all,
I have a prospect table in my database with a primary key identity field called prspct_id. When a new prospect is created through an Access front-end, the prospect is automatically assigned a prspct_id.
In an Access application I am writing, a new prospect is created with an insert statement. I then need to capture the prspct_id for updates in other tables. I am currently doing this by opening an ADO recordset with "SELECT MAX(prspct_id) FROM prospct" immediately after creating the prospect.
I am concerned, however, that in the time it takes to open that recordset another prospect could be created by another process so that MAX(prspct_id) is no longer the one I want. This must be a common situation.
Any advice on dealing with this greatly appreciated!
Jonathan
January 7, 2003 at 11:10 am
Not sure if you can get it in Access, but you can get @@scope_identity (select it) to get the last identity value you inserted.
Steve Jones
January 7, 2003 at 11:16 am
quote:
Not sure if you can get it in Access, but you can get @@scope_identity (select it) to get the last identity value you inserted.Steve Jones
http://www.sqlservercentral.com/columnists/sjones
Steve, sorry minor correction : It is not @@scope_identity but scope_identity()
as the first one denotes a global variable, and the second one is the function
that is only available in SQL Server 2000 unfortunately.
Tim C.
//Will write code for food
Tim C //Will code for food
January 7, 2003 at 11:23 am
Oops, just read that he is using an Access front end. If the back end is SQL Server 2000, he can use the scope_identity, and using it from a stored proc with an output parameter would be the optimal use, but if he if intent on dynamic sql the commands can be concatenated if the provider allows it.
Tim C.
//Will write code for food
Tim C //Will code for food
January 7, 2003 at 1:22 pm
Thanks for the replies,
Unfortunately, I am using SQL 7 (I wish they would spend some $ around here). It sounds like a sp with a return value is the way to go.
Thanks again.
January 7, 2003 at 3:03 pm
In the case of SQL 7 you will use @@IDENTITY as soon as the insert occurrs in your SP. However it may stil be affected by a later INSERT if you have a high number of transactions.
January 7, 2003 at 3:15 pm
Watch out for the problem with using @@IDENTITY with a table that has a trigger that inserts into another table that has an identity. Then you are back to selecting MAX. If you do have to select MAX, take a look at setting your transaction isolation level.
Tim C.
//Will write code for food
One Windows OS to rule them all, One Windows OS to find them,
One Windows OS to bring them all and in the darkness bind them
In the Land of Microsoft where the Shadows lie.
Tim C //Will code for food
January 9, 2003 at 10:32 am
No Antares - @@IDENTITY is safe as long as your own connection did not perform the insert into another table with an Identity column. @@IDENTITY is specific to the session connection. TCartWright is correct in saying watch out for triggers, because they are the only pieces where you might be adding to another table.
If you have the luxury, JonathanM, you can always perform the insert via Stored Procedure. Rather than using triggers to perform additional work, the extra can be done within this stored proc. After the table insert, grab @@IDENTITY. Now perform any updates to other tables that are necessary. Select the variable containing the identity value as the recordset returned by the proc.
something like:
DECLARE
@masterIdent
INSERT INTO <master>
SELECT @masterIdent = @@IDENTITY
INSERT INTO <details>
SELECT @masterIdent 'InsertIdent'
RETURN 0
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply