retieveing id from something just inserted

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)

retieveing id from something just inserted

Author

Topic

Swede
Yak Posting Veteran

74 Posts

Posted - 2002-03-22 : 04:27:00

If I insert into a table the following:
insert_id (key)
insert_title
etc...

Is there any way of retrieving the inserted insert_id back? All done in a stored procedure...

=====================================
Why not try and do the impossible?

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2002-03-22 : 05:48:24

if the key is an auto-number or identity field then the variable @@identity will contain the value of the last record inserted via that Stored Procedure.

yakoo
Constraint Violating Yak Guru

312 Posts

Posted - 2002-03-22 : 11:10:22

Depeding on exactly what you want returned there are three functions to return Identities. They are SCOPE_IDENTITY(), IDENT_CURRENT(), and @@IDENTITY.

I have always used SCOPE_IDENTITY() becuase of this from bol

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. For more information, see IDENT_CURRENT.

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.

For example, you have two tables, T1 and T2, and an INSERT trigger defined on T1. When a row is inserted to T1, the trigger fires and inserts a row in T2. This scenario illustrates two scopes: the insert on T1, and the insert on T2 as a result of the trigger.

@@IDENTITY will return the last IDENTITY column value inserted across any scope in the current session, which is the value inserted in T2.

*****SCOPE_IDENTITY() will return the IDENTITY value inserted in T1, which was the last INSERT that occurred in the same scope.

***** is why you would most likely use it

Edited by - yakoo on 03/22/2002 11:12:04

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-03-22 : 11:13:10

I think SCOPE_IDENTITY (and IDENT_CURRENT) is only available in SQL 2000.

Swede
Yak Posting Veteran

74 Posts

Posted - 2002-03-24 : 14:02:04

Alright :)

But how do I actually use it? :)

=====================================
Why not try and do the impossible?

LarsG
Constraint Violating Yak Guru

284 Posts

Posted - 2002-03-24 : 15:44:24

Just as any other value expression, e. g.

set @last_value = scope_identity()
-- or
insert into oapiey values(@@identity,current_timestamp)

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2002-03-24 : 17:36:03

You could also read the article we've got on identity columns: http://www.sqlteam.com/item.asp?ItemID=8003

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.

Swede
Yak Posting Veteran

74 Posts

Posted - 2002-03-25 : 10:17:53

Thanks guys :D

=====================================
Why not try and do the impossible?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources