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

2 comments:

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

    ReplyDelete
  2. thanks for the feedback. Amended accordingly

    ReplyDelete