By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,853 Members | 1,034 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,853 IT Pros & Developers. It's quick & easy.

Get the field value after INSERT

P: n/a
Hi,

Problem:
I need to get the value of auto-incremented field from just inserted
record

In Oracle this is INSERT .. RETURNING command.
In SQL Server there are @@IDENTITY, IDENT_CURRENT, SCOPE_IDENTITY

- @@IDENTITY returns the value from the very LAST insert on any table
involving in the insert process ( including triggers ) ,
so this value may have nothing to do with my table

- IDENT_CURRENT returns the last identity value generated for a
specific table in any session and any scope,
so this value may come not from my session

- SCOPE_IDENTITY returns the last identity value generated for any
table in the current session and the current scope , but from the very
LAST insert command ( that may be some INSERT in the audit tables)
so it may have nothing to do with my table

Question :
- Is there any trusted way I can get the value of auto-incremented
field
in my table and in the scope of my session?
Thanks, Eugene
Jul 23 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Why doesn't SCOPE_IDENTITY() meet your requirements? Retrieve it
immediately after the INSERT in the current scope. If you also insert
to audit tables then you just need to retrieve SCOPE_IDENTITY() before
that insert rather than after. You can assign the value to a variable
and then return that variable as an output parameter from a stored
procedure if you need to.

--
David Portas
SQL Server MVP
--

Jul 23 '05 #2

P: n/a
Eugene (yg********@hotmail.com) writes:
- @@IDENTITY returns the value from the very LAST insert on any table
involving in the insert process ( including triggers ) ,
so this value may have nothing to do with my table
Not any table, only inserts to table that has an IDENTITY column matters.
Which may be problematic rnough.
- IDENT_CURRENT returns the last identity value generated for a
specific table in any session and any scope,
so this value may come not from my session
Yes.
- SCOPE_IDENTITY returns the last identity value generated for any
table in the current session and the current scope , but from the very
LAST insert command ( that may be some INSERT in the audit tables)
so it may have nothing to do with my table


As David said, retrieve the value directly after the INSERT you are
interested in.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.