Rephrased IDENTITY question - 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 2000 Forums

SQL Server Development (2000)

Rephrased IDENTITY question

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2002-08-26 : 10:03:04

CREATE TABLE Org (
OrgID int NOT NULL IDENTITY (100,1) ,
MyIdent INT NOT NULL ,
MyIdent_Current INT NOT NULL
)

INSERT INTO Org VALUES (@@IDENTITY, Ident_Current('Org'))
INSERT INTO Org VALUES (@@IDENTITY, Ident_Current('Org'))
INSERT INTO Org VALUES (@@IDENTITY, Ident_Current('Org'))
SELECT * FROM Org

Gives the following results:

OrgID,MyIdent,MyIdent_Current
100,103,100
101,100,100
102,101,101
103,102,102

Neither MyIdent or MyIdent_Current match the OrgID. Notice that adding +1 would not work in all 4 cases either.

Any way to get the IDENTITY of the row to be inserted?

SamC

Edited by - SamC on 08/26/2002 13:35:27

SamC
White Water Yakist

3467 Posts

Posted - 2002-08-26 : 14:33:09

Problem seems to be solvable as follows:

On new table creation: @@IDENTITY IS NULL
On truncated table: @@IDENTITY REMAINS AS IS TABLE NOT TRUNCATED.

Handle either of these situations, then for all others:

INSERT identity = Ident_Current('Org')+1

SamC

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-08-26 : 21:04:43

OK, now I'm curious, why do you need the IDENTITY of the row you are about to insert as opposed to the row that was just inserted? Especially since an insert by another process between the time you check the value and the time you do the insert will change the actual value used.

If it's just a manual check you want to do, you can use DBCC CHECKIDENT. You can also use that command to change the next value (e.g. reset it to the seed value after a delete or truncate).

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources