INSERT with a return? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

INSERT with a return?

Author

Topic

hblackorby
Starting Member

3 Posts

Posted - 2002-06-21 : 14:49:12

Is there any way to run an INSERT on a table, and then have it returned the generated Identity ID? I have a table with an identity ID, and whenever I add a row, it increments that field (obviously). I'd like to be able to INSERT and then return the newly created ID in one statement, if possible, to elminate any problems I might have with a delay between INSERTing the row and retrieving the most recent ID. I have a feeling if someone were to run the webpage almost at the same time, you might retrieve someone else's newly created ID because of the slight delay in running two Dynamic queries in an ASP page. Maybe this really isn't an issue though. Let me know.

INSERT INTO People (Name,Email) VALUES('Name1','Email1')

then

SELECT MAX(ID) FROM People

Is there a better way than that?

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-06-21 : 14:54:13

quote:

INSERT INTO People (Name,Email) VALUES('Name1','Email1')

then

SELECT MAX(ID) FROM People

Is there a better way than that?

Yes.
INSERT INTO People (Name,Email) VALUES('Name1','Email1')
then
return scope_identity()

setBasedIsTheTruepath
<O>

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-06-21 : 14:58:13

Look up @@IDENTITY in BOL
If on SQL 2000 use SCOPE_IDENTITY()

quote:
IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the value generated for a specific table in any session and any scope.

SCOPE_IDENTITY and @@IDENTITY will return last identity values generated in any table in the current session. However, SCOPE_IDENTITY returns values inserted only within the current scope; @@IDENTITY is not limited to a specific scope.

HTH
Jasper Smith

hblackorby
Starting Member

3 Posts

Posted - 2002-06-21 : 15:04:35

Thanks for the suggestion, but SCOPE_IDENTITY is only available in SQL Server 2000. I'm using SQL Server 7.0. I should have said that in the first post. Any other suggestions?

hblackorby
Starting Member

3 Posts

Posted - 2002-06-21 : 15:15:23

Thanks for the sugesstions. I think'll be fine now. Obviously the only true way to return the value is with a function/stored procedure. I think the @@IDENTITY will work for me.

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-06-21 : 15:16:24

quote:

Thanks for the suggestion, but SCOPE_IDENTITY is only available in SQL Server 2000. I'm using SQL Server 7.0. I should have said that in the first post. Any other suggestions?

Then @@IDENTITY, but you should be aware that it returns the last identity value obtained on the connection regardless of scope. This means that if you inserted into a table which had an insert trigger, and DML in that insert trigger in turn inserted into a table w/ an identity column, @@IDENTITY would return that last value, the identity that came from the trigger insert rather than the base table insert.

Jonathan Boott, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources