Create ID Table On The Fly - 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)

Create ID Table On The Fly

Author

Topic

ywb
Yak Posting Veteran

55 Posts

Posted - 2006-03-03 : 18:52:36

Hi,

How can I create using t-SQL a table which has only 1 column of incrementing integer from 1 to 8000 (ie. 8000 rows)?

ywb

MichaelP
Jedi Yak

2489 Posts

Posted - 2006-03-03 : 19:00:08

Here ya go. This code has some provisions in it for a min, max, and a list of exceptions that I wanted to leave out of my table. You should be able to do some quick modifications to this and make it work for ya.


Create Table TallyTable(
       Tally int
)
SET NOCOUNT ON
Declare @i int
Set @i = 101
WHILE @i <= 9998
     BEGIN
	SELECT @i  = CASE WHEN @I in (2222,3333,4444,5555,6666,7777,8888) THEN  @i + 1 ELSE @i END 

         Insert Into TallyTable Values (@i)
         Set @i = @i + 1
     END
SET NOCOUNT OFF

Michael

<Yoda>Use the Search page you must. Find the answer you will. Cursors, path to the Dark Side they are. Avoid them, you must. Use Order By NewID() to get a random record you will.</Yoda>

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-03 : 19:10:36

[code]select NUMBER from dbo.F_TABLE_NUMBER_RANGE(1,8000)[/code]
the F_TABLE_NUMBER_RANGE FROM http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685

----------------------------------
'KH'

ywb
Yak Posting Veteran

55 Posts

Posted - 2006-03-03 : 19:41:39

Thanks, guys!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources