How to get the inserted row autoincrement id?

Question

In C# + SQL Server ExecuteScalar() not returning last inserted id I found a nice solution to get the inserted row autoincrement id. So now my query looks just like:

INSERT INTO dbo.YourTable(Col1, Col2, ..., ColN)   
OUTPUT Inserted.ID
VALUES(Val1, Val2, ..., ValN);

But unfortunately I am on SQL Server 2000 and this syntax is not available.

What can I use there to achieve the same result?

Answer 1

You can use @@IDENTITY in Sql Server 2000

However you need to be aware of the limitations (which were overcome by SCOPE_IDENTITY). See this article for an explanation http://blog.sqlauthority.com/2007/03/25/sql-server-identity-vs-scope_identity-vs-ident_current-retrieve-last-inserted-identity-of-record/

EDIT

MSDN http://msdn.microsoft.com/en-us/library/aa259185%28v=sql.80%29.aspx says that SCOPE_IDENTITY is avaiable in SS2000, so I recomment that you use it instead.

You can get the value by executing SET @<var> = SCOPE_IDENTITY()

This link http://www.mikesdotnetting.com/Article/54/Getting-the-identity-of-the-most-recently-added-record has some more examples of how to use SCOPE_IDENTITY