Get newly created id from database

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)

Get newly created id from database

Author

Topic

foamy
Starting Member

12 Posts

Posted - 2008-11-12 : 07:34:48

This is something that has always bothered me.

What is the best way of getting the newest ID from a database for use in my application?

Here's the scenario:

I have a function for creating a new employee. This executes this sql statement:
INSERT INTO Employee(Name, Phone) VALUES(@Name, @Phone)

My database will create an ID for this record. I know I can get this ID using the SELECT MAX() statement, but, in theory, another record could have been created in the mean time, which would make the ID I retrieve useless...

So, is there a way to have an INSERT command return the created ID?

(I'm using SQL Server 2005)

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2008-11-12 : 07:55:41

CREATE TABLE Employee (
ID int IDENTITY (1, 1),
Name VARCHAR(100),
Phone VARCHAR(50) )

INSERT INTO Employee(Name, Phone) VALUES(@Name, @Phone)

NeilG
Aged Yak Warrior

530 Posts

Posted - 2008-11-12 : 08:01:00

There are two ways to hold this information and stop it being changed from other transactions interfering

Declare a parameter i.e @Indent and the straight after the insert run a select @Ident = @@identity, this however is not the best method type using the

Scope_identity() function this function will only return the last identity value create within that current scope for example if you are running a stored proc then this function will return the last ident value that has been created in that stored proc and will not be affected by transactions that happen outside of that scope

NeilG
Aged Yak Warrior

530 Posts

Posted - 2008-11-12 : 08:03:39

lionofthedezert, this is the second post that i have seen you more or less just repeat the sql that the questioner has posted which doesnt have any relevence to the actual question's potential answer

foamy
Starting Member

12 Posts

Posted - 2008-11-12 : 08:07:19

Could you give an example of how to use Scope.Identity() in TSQL?

something like

BEGIN
INSERT
GET ID <- how should this look?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-12 : 08:52:14

[code]CREATE TABLE Employee (
ID int IDENTITY (1, 1),
Name VARCHAR(100),
Phone VARCHAR(50) )

DECLARE @LastInsertedID int

INSERT INTO Employee(Name, Phone) VALUES(@Name, @Phone)

SET @LastInsertedID=SCOPE_IDENTITY()--this will give you last id value

SELECT @LastInsertedID[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-12 : 08:55:40

Also refer this to understand different ways of getting identity value

http://blog.sqlauthority.com/2007/03/25/sql-server-identity-vs-scope_identity-vs-ident_current-retrieve-last-inserted-identity-of-record/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources