Last ID - 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

General SQL Server Forums

New to SQL Server Programming

Last ID

Author

Topic

barnabeck
Posting Yak Master

236 Posts

Posted - 2012-05-21 : 08:24:52

Sorry this issue seems to be a classic one, but I now spent an hour on it and can't get a clear answer; at least all suggestions do not work for me.

How do I get the latest assigned ID of a table? I start a new session and @@IDENTITY just returns NULL. SCOPE_IDENTITY is not recognized.

Regards,
Martin

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-05-21 : 09:07:55

TRY SCOPE_IDENTITY()

Jim

Everyday I learn something that somebody else already knew

barnabeck
Posting Yak Master

236 Posts

Posted - 2012-05-21 : 09:09:40

yes, I noticed in the meantime that I had forgotten the brackets... but anyway it returns NULL

Thank you anyway,
Martin

barnabeck
Posting Yak Master

236 Posts

Posted - 2012-05-21 : 10:40:15

I can get the ID after an INSERT, but there must be a way to it even without... or not?

INSERT INTO tickets ([number],[titel]) VALUES (2,'test');
GO
SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY];
GO

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-05-21 : 10:46:29

No. SCOPE_IDENTITY() gives you the last inserted IDENTITY of your current scope. If you want the ID of the last inserted record, you will need some type of date/time column to order by.


SELECT TOP 1 [ID field] FROM yourtable ORDER BY [date field] desc

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

barnabeck
Posting Yak Master

236 Posts

Posted - 2012-05-21 : 11:21:58

Great. At least this is a clear statement.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-05-21 : 11:25:58

There is also DBCC CHECKIDENT.

DBCC CHECKIDENT ("TableName", NORESEED);

X002548
Not Just a Number

15586 Posts

Posted - 2012-05-21 : 11:34:45

quote:
Originally posted by barnabeck

Great. At least this is a clear statement.

ummm...not really...by the time that you do the select...you may be out of scope..hence grabbing the wrong id

You need to ASSIGN the id from the scope to a variable when you do the insert...not just do a select

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources