Recursive Queries using Common Table Expressions (CTE) in SQL Server

If you had the need to create a table with index data quickly you can use this script:

WITH tmpData AS
(
SELECT 1 as iIndex
UNION ALL
SELECT iIndex + 1 FROM tmpData WHERE iIndex <=100 ) SELECT * FROM tmpData;

This works for Dates as well:

WITH tmpData AS
(
SELECT CONVERT('2009-01-01',DATETIME) As DateValue
UNION ALL
SELECT DATEADD(dd,1,DateValue) FROM tmpData WHERE DateValue < '2010-01-01' ) SELECT * FROM tmpData;

The Source of this article can be found here;