Friday, October 1, 2010

Number Table

Number table or tally table as it is also known as, is a very useful table in my day to day query. There are lots of way to do that. In my other post, i uses a numbers table. Here is how i create the numbers table
create table numbers 
    num     int, 
    primary key (num)

And to populate it with numbers, a recursive cte is used.

; with num as
    select    n = 0
    union all
    select    n = n + 1
    from    num
    where    n     < 1000
insert into numbers (num)
select    n
from    num option (maxrecursion 0)

Here i inserted number 0 to 1000. You can change the query to any number you required


  1. Above query will insert only 100 numbers as maximum recursion is limited to 100.

  2. thanks for the feedback. Amended accordingly