The concept of a CTE, or common table expression, is something new in SQL 2005, and it is hugely powerful for things such as traversing trees or hierarchies, among other things.
You can use a CTE to generate an ad-hoc Numbers Table. Try this:
|
1 |
<span class="kwrd">WITH</span> Numbers(n)<br /><span class="kwrd">AS</span><br />(<br /><span class="kwrd">SELECT</span> 1 <span class="kwrd">AS</span> n<br /><span class="kwrd">UNION</span> <span class="kwrd">ALL</span><br /><span class="kwrd">SELECT</span> (n + 1) <span class="kwrd">AS</span> n<br /><span class="kwrd">FROM</span> Numbers<br /><span class="kwrd">WHERE</span><br />n < 1000<br />)<br /><span class="kwrd">SELECT</span> n <span class="kwrd">FROM</span> Numbers <br /><span class="kwrd">OPTION</span>(MAXRECURSION 1000) |
That will generate numbers to 1000. Change the comparison expression, and raise up the max recursion depth, to go to a higher number. Or, instead of using the CTE in your SQL, you could just do a SELECT INTO and dump it into a table.
If your not sure why you would want a numbers table, just think about it a bit, or go Google! A numbers table is fantastically useful for all sorts of tasks in a database, including parsing text, joining against to create multiples, and more.