Easy way to generate number table

Often you may require number table for various purposes (parsing csv, string manipulation,etc)

If you use SQL Server 2005, then it is very easy than any other method you use in lower versions

Here are two methods of generating number table with 10000 numbers

(1) Use Row_number() funtion



Declare @numbers table(number int)


insert into @numbers(number)

select number from

(
select row_number() over (order by s1.name) as number 

from sysobjects s1 cross join sysobjects s2

) as numbers
where number between 1 and 10000


select number from @numbers



(2) Use CTE



Declare @numbers table(number int)


with numbers(number) as

(

select 1 as number

union all

select number+1 from numbers where number<10000
)


insert into @numbers(number)
select number from numbers option(maxrecursion 10000)
select number from @numbers
Published 06 November 2007 12:27 by Madhivanan
Filed under: , ,

Comments

# re: Easy way to generate number table

07 November 2007 10:04 by Craig_SQL

I would be wary of using the first script - it will work well on systems with a large number of objects and hence rows in the sysobjects table. On my database it generated a total number of 3814 rows, just a little shy of the 10,000 required.

# re: Easy way to generate number table

07 November 2007 10:06 by Craig_SQL

Update: I tried this version of script 1 on an empty database and it generated 10,000 rows:

Declare @numbers table(number int)

insert into @numbers(number)

select number from

(

select row_number() over (order by s1.name) as number

from sysobjects s1 cross join sysobjects s2 cross join sysobjects s3

) as numbers

where number between 1 and 10000

select number from @numbers

# re: Easy way to generate number table

07 November 2007 12:42 by Madhivanan

Yes. If you want to generate table with higher numbers you should cross join sysobjects with more than one time