ansaurus

Question

How to 'select' the current identity value for a table?

Answer 1

+5 A:

SELECT IDENT_CURRENT('MyTable')

See BOL

AdaTheDev 2010-10-28 13:07:45

Excellent, I knew there had to be a way to do it. Thanks :)

Binary Worrier 2010-10-28 13:24:08

Answer 2

A:

 select @@identity

might work,

there is also

SELECT SCOPE_IDENTITY()

Orbit 2010-10-28 13:07:54

why -1? ....... perfectly valid

Orbit 2010-10-28 13:09:57

I'm guess the downvote, because that returns the identity of the row you just inserted. I'm not inserting any rows, but need to know the current identity value of the table

Binary Worrier 2010-10-28 13:11:36

This is not correct. @@identity can be for any table. See http://msdn.microsoft.com/en-us/library/aa933167(SQL.80).aspx. **@@IDENTITY and SCOPE_IDENTITY will return the last identity value generated in any table in the current session. **

astander 2010-10-28 13:12:29

although IDENT_CURRENT seems to be the correct answer here.

Orbit 2010-10-28 13:12:55

You don't specify the table to get the last identity from. So you can't get the identity of any table. It's always the table where you have inserted some rows just before.

Stefan Steinegger 2010-10-28 13:54:46

Answer 3

+4 A:

SELECT IDENT_CURRENT('TABLE_NAME')

returns NULL if there is no identity defined in the table

il_guru 2010-10-28 13:07:59

ansaurus

tags:

views:

answers:

How to 'select' the current identity value for a table?

related questions