autogenerate column - 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

Transact-SQL (2000)

autogenerate column

Author

Topic

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2007-02-23 : 09:45:42

I am trying to AUTOGENERATE an ID column doesnt seem to work

tsql statement below is stuck in an infinite loop.

CREATE TABLE [CASE] (ID INT)

INSERT INTO [CASE] VALUES (1)

DECLARE @CURRENTID INT
SELECT @CURRENTID = MAX([ID])
FROM [CASE]
WHILE @CURRENTID <= 100
BEGIN
INSERT INTO [CASE]
SELECT @CURRENTID+1 FROM [CASE]

SELECT @CURRENTID = MAX(ID)
FROM [CASE] WHERE [ID] < 100
END

any suggestion, inputs would help

Thanks

Andraax
Aged Yak Warrior

790 Posts

Posted - 2007-02-23 : 10:08:23

Hey. A couple of problems here:

1) SELECT @CURRENTID+1 FROM [CASE] <- This will be more than one row after a while ;)

2) SELECT @CURRENTID = MAX(ID) FROM [CASE] WHERE [ID] < 100 <- This will result in the value stopping at 99, hence an indefinate loop.

This is a corrected version:

CREATE TABLE [CASE] (ID INT)

INSERT INTO [CASE] VALUES (1)

DECLARE @CURRENTID INT

SELECT @CURRENTID = MAX([ID])
FROM [CASE]

WHILE @CURRENTID <= 99
BEGIN
INSERT INTO [CASE]
SELECT @CURRENTID+1

SELECT @CURRENTID = MAX(ID)
FROM [CASE] WHERE [ID] <= 100
END

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2007-02-23 : 10:57:34

Thanks Andraax that did the trick

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources