In SQL server @@identity is visible to the connection that did the insert and returns tha last identity inserted. But here is the problem:
It returns the LAST identity inserted.
-If you did multiple inserts in one go, you get the last only.
-If the table you do insert have trigger doing inserts in other tables then still you get the last identity inserted (and you would never know which table's identity it is).
I have never been a fan of identity and I try to keep away from using identity values. My preference is GUID. I find myself that if I use identity and do multiple inserts either I choose not to get back identity values assigned, or before submitting attach GUID values to track the rows that I inserted. A pain that is not to worth to take when there is GUID instead.
Cetin
>PMFJI, in the database that I use (sybase Sql Anywhere) @@identity is only visible to the connection that did the insert. Is MS Sql different than that?
>
>>>Hi everybody,
>>>
>>>We're using SPT and then a special program to make cursor updatable. I see this code to get the last inserted value
>>>
>>> mysqlexec("select @@identity as keyval",'csrKeyVal', program())
>>>
>>>which doesn't look entirely correct to me - who guarantees that by doing a separate query you'll get the value of the record you just inserted and not someone else in between?
>>>
>>>Is there a better way to get the latest value?
>>>
>>>Thanks in advance.
>>
>>Of course it is not correct. You cannot even guarantee it doing in the same query batch. There are other and reliable ways like:
>>
>>select scope_identitity()
>>
>>select ... from inserted
>>
>>output clause
>>
>>Cetin