Last identity value inserted in a table ms sql 2005
I have a query that instert a record into a table. Th record has an identity field,id.
As soon as the record has been inserted I need to know the id value some relational purposes.
I can use the max function, but since records will be inserted to by many users I need to make sure that the id I get is the one corresponding to the record inserted, with no cnfusion between 2 records inserted almost simultaneoulsy.
I would like to insert and have sql return unambigously the id of the insterted record.
As soon as the record has been inserted I need to know the id value some relational purposes.
I can use the max function, but since records will be inserted to by many users I need to make sure that the id I get is the one corresponding to the record inserted, with no cnfusion between 2 records inserted almost simultaneoulsy.
I would like to insert and have sql return unambigously the id of the insterted record.
select @@identity returns last inserted value
ASKER
Thx. The insert query is: Req table has an id int identity value
where do I insert the SELECT SCOPE_IDENTITY() AS last_inserted_value;?
Set ReqSet = Server.CreateObject("ADODB
sql="insert into req (hosp) values ('test')
ReqSet.Open sql, cn
Set ReqSet=Nothing
where do I insert the SELECT SCOPE_IDENTITY() AS last_inserted_value;?
Set ReqSet = Server.CreateObject("ADODB
sql="insert into req (hosp) values ('test')
ReqSet.Open sql, cn
Set ReqSet=Nothing
ASKER
Or the select @@identity ?
What is the difference between select @@identity and SELECT SCOPE_IDENTITY() AS last_inserted_value;
What is the difference between select @@identity and SELECT SCOPE_IDENTITY() AS last_inserted_value;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Two users insert a record almost simultaneoully with their own data, I need to relate the id of the inserted record to the user. I am afraid that 2 records inserted almost simultaneously would give rise to confusion as to which belongs to what user.
The term session consues me a bit in his context
The term session consues me a bit in his context
Ok.
From SQL Server, users can connect to SQL Server via a session and a session is exclusive for a single user.
So, you can use SCOPE_IDENTITY() or @@identity to gather the last inserted value from the session (ie., for a specific user) without any issues.
>> I am afraid that 2 records inserted almost simultaneously would give rise to confusion as to which belongs to what user.
No need to worry about that as mentioned above..
SCOPE_IDENTITY() or @@identity obtained from the specific session would give the correct value for the particular user.
From SQL Server, users can connect to SQL Server via a session and a session is exclusive for a single user.
So, you can use SCOPE_IDENTITY() or @@identity to gather the last inserted value from the session (ie., for a specific user) without any issues.
>> I am afraid that 2 records inserted almost simultaneously would give rise to confusion as to which belongs to what user.
No need to worry about that as mentioned above..
SCOPE_IDENTITY() or @@identity obtained from the specific session would give the correct value for the particular user.
ASKER
Great.
Where do I insert ti?
Where do I insert ti?
>> Where do I insert ti?
Why are you trying to insert it in some table..
The values are already inserted into your actual table and kindly explain what's the need to insert it again into another table.
You can simply obtain those values (if required) for some manipulations right..
Why are you trying to insert it in some table..
The values are already inserted into your actual table and kindly explain what's the need to insert it again into another table.
You can simply obtain those values (if required) for some manipulations right..
ASKER
Yes...
I mean not insert in a table but code in the query.... sorry
I mean not insert in a table but code in the query.... sorry
then you can pass it to a variable and then fetch values from your application code..
Not familiar in front end code and hence some others can help you on that..
Not familiar in front end code and hence some others can help you on that..
ASKER
Great help... fast and acurrate
Insert into ur_table (id)
values ('some_value');
SELECT SCOPE_IDENTITY() AS last_inserted_value;