You have several options open to you....
SCOPE_IDENTITY
@@IDENTITY
IDENT_CURRENT
SCOPE_IDENTITY and @@IDENTITY have different uses. There is no benefit to
using one over the other. They both return the last value inserted into an
Identity column. However, the logic they use to determine the value is
different.
Take this scenario:
You have 2 tables, Table1 and Table2. An insert into Table1 triggers an
insert into Table2 via an Insert trigger. @@INDENTITY will return the value
of the IDENTITY in Table1, SCOPE_IDENTITY will return the value of the
Identity in Table2. This is because SCOPE_IDENTITY is limited to the current
scope, while @@IDENTITY is limited to the current session. @@IDENTITY then
returns the IDENTITY value from Table2.
In a scenario where you are inserting into a single table, they will both
return the same value. Of course, if you are inserting itno related tables
in a transaction, or there are Insert triggers involved, you need to be
careful you get the value from the correct table!
For the sake of discussion, there is also an IDENT_CURRENT. It limits itself
to a particular table across all scopes. It is used like this:
IDENT_CURRENT('table_name')
In summary:
IDENT_CURRENT returns the last identity value generated for a specific table
in any session and any scope.
@@IDENTITY returns the last identity value generated for any table in the
current session, across all scopes.
SCOPE_IDENTITY returns the last identity value generated for any table in
the current session and the current scope.
HTH
N
-----Original Message-----
From: Ken [mailto:[EMAIL PROTECTED]
Sent: 02 June 2006 14:21
To: CF-Talk
Subject: Fetching last record
Hi. I am doing a Insert statement into my db, and immediately after
the Insert I need to fetch the value of the field ID (Identity).
I know I could do a Select Max(ID), but how else can I fetch the value
of the last inserted record? DB: SQL 2000.
Please help.
- Ken.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:242027
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=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
