Subscribe: Madhivanan : number
http://sqlblogcasts.com/blogs/madhivanan/rss.aspx?Tags=number&AndTags=1
Preview: Madhivanan : number

Madhivanan : number



Tags: number



 



Easy way to generate number table

Tue, 06 Nov 2007 12:27:00 GMT

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 numberswhere 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[...]