Tuesday 4 December 2012

Creating a list of numbers in TransactSQL


To create a simple list of  numbers in an SQL table,
(from http://www.simple-talk.com/sql/t-sql-programming/faking-arrays-in-transact-sql/), the following TransactSQL does the job for a small number of rows (fewer than 32768)


WITH Nbrs ( n ) AS (
        SELECT 1 UNION ALL
        SELECT 1 + n FROM Nbrs WHERE n < 500 )
    SELECT n FROM Nbrs
    OPTION ( MAXRECURSION 500 )


Alternatively (for higher row counts):

declare @i int
declare @rows_to_insert int
set @i = 0
set @rows_to_insert = 1000

while @i < @rows_to_insert
    begin
    INSERT INTO #temp VALUES (@i)
    set @i = @i + 1
    end

No comments:

Post a Comment