get max insert 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

SQL Server 2005 Forums

Transact-SQL (2005)

get max insert id

Author

Topic

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2008-10-20 : 07:02:16

i am inserting few rows each time to a table (where the table has a column ID with autoincrement) - how can i get the max(id) of an insert?

Thanks
Peleg

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-20 : 07:04:54

do you have any audit columns like datecreated? else you might have to retrieve like below

SELECT top 1 id from table order by id desc

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2008-10-20 : 07:34:31

if i try to get for example ScopeIDENTITY() - which ID will i get?

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-20 : 07:46:22

quote:
Originally posted by pelegk2

if i try to get for example ScopeIDENTITY() - which ID will i get?

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

last id inserted in the scope. which will also be same.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-20 : 09:31:37

or use

select ident_current('table_name')

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-20 : 09:41:58

refer this to understand different functions for getting idntity value

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

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-10-21 : 08:43:32

Out of interest, why would you care? The whole idea of identity columns is to give you a unique number. Assuming you can even get that information you should not really be using it becuause it will probably not be true in the context of a transaction.

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-21 : 10:04:35

Sometimes people wants to pull the next available number to a NULLable column such an incident number to a list of jobs.


UPDATE tab1 set 
COL1 = (select top 1 ID from Tab2 order by desc) with(tablock)

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-10-21 : 10:17:59

well not only is that useless but it wont work unless you lock the entire table for the duration of the transaction. That may be ok for a little stand alone application, but for serious throughput you're just fucking yourself over. To use your example, you now have contention and serialisation on the read table as well as the write table. Completely useless.
If you need the number generated by an identity then use @@scope_identity after you insert the row in the context of a transaction. Nothing else makes sense.

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-21 : 10:24:27

You need some love man...

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-10-21 : 18:46:42

You think? I was just pointing out some problems with your proposed solution. No offence intended. No extra love needed.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources