MS-SQL Auto increment?

Anon

Hey,

Anybody know how to get the auto incremented number from MS-SQL? I know it has the facility, but I can't figure out how to read it.

Any ideas?

Thanx

-- John G.

Anon

The auto-increment is called an Identity column, you can use:

select @@identity to get the last identity column value for any table,
select IDENT_CURRENT(<table>) to get the last inserted identity for the passed table
select SCOPE_IDENTITY() to get the last identity inserted within the current scope.

Enjoy!
Graham

Anon

Thanks, Graham!

One more question on this - when do you call the select statements above? Before, during or after the original query?

Would it be a separate query, or part of a transaction?

Many thanks for your help so far...

--- John G.

Anon

what about using idetify in creating table?
i know its like this...

CREATE TABLE transfers
(
TransferID int IDENTIFY (100,1),
Type varchar(20),
Player varchar(40),
Team char(40),
Price varchar(5),
dateoftransfer datetime,
Officiality varchar(20)
)

but its syntax error? i heard there is autonumber how to use that? tnx

Anon

The syntax is "identity" not "identify" also the column must be not null, so do this:

create table mytable (col1 int not null identity, col2 varchar(20))

etc

enjoy
graham

Anon

sorry the reply took so long, but all these queries should be executed after the initial insert using the same connection.

graham