How to get IDENTITY of inserted row

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 2005 Forums

Transact-SQL (2005)

How to get IDENTITY of inserted row

Author

Topic

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2008-08-20 : 09:24:27

Hi,

A simple example if I have this table with id, name, address columns, id is the identity.

INSERT INTO table (name,address) VALUES ('john','new street')

How do I get the id of the row I just inserted. At the moment i'm doing this straight after.

SELECT MAX(id) FROM table

It works but is there a possibility that with a busy system 2 inserts might happen before the first ID is retrieved?

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-20 : 09:25:02

use SCOPE_IDENTITY()

DECLARE @LatestID int

SET @LatestID=SCOPE_IDENTITY()

SELECT @LatestID

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-20 : 09:25:42

another way is to use OUTPUT clause

INSERT INTO table (name,address) 
OUTPUT INSERTED.id
VALUES ('john','new street')

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2008-08-20 : 09:34:02

Hi, thanks.

I'm using the .NET SqlCommand.ExecuteNonQuery method - how would I get the output value?

The documentation for that class says it returns an integer of the number of rows affected?

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-20 : 09:39:45

Try this out:-
http://www.devnewsgroups.net/group/microsoft.public.dotnet.framework.adonet/topic13967.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources