CODEWITHSUNDEEP
sqlsql-server-2000
horgh
horgh

Reputation: 18534

How to get the inserted row autoincrement id?

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?

Upvotes: 2

Views: 1154

Answers (2)

horgh
horgh

Reputation: 18534

The answer I have working is to insert rows through a stored procedure, as OUTPUT keyword is not available in ms sql server 2000. Also I had to abandon inserted by the same reason and used scope_identity.

Well, it looks somewhat like this:

CREATE PROCEDURE [dbo].[InsertRow]      
    @pDate datetime,
    @pId int OUTPUT
AS  
BEGIN   
    SET NOCOUNT ON;

    INSERT INTO nl$RegisterRace ([Date],...)
    VALUES (@pDate,...);

    SET @pId = scope_identity();
END

Upvotes: 0

DeanOC
DeanOC

Reputation: 7282

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

Upvotes: 2

Related Questions