Thursday, July 15, 2010

T-SQL: Script to Populate a Dates Table With Recursion and a CTE

I used to create dates tables like this:

--********************************
DECLARE @BeginDate datetime
DECLARE @EndDate datetime

SET @BeginDate = '1/1/1900'
SET @EndDate = '12/31/2050'

CREATE TABLE #Dates ([date] datetime)

WHILE @BeginDate <= @EndDate
BEGIN
INSERT #Dates
VALUES
(@BeginDate)

SET @BeginDate = @BeginDate + 1
END
SELECT * FROM #Dates
DROP TABLE #Dates
--********************************

That works fine, but it has a lot to clean up, executes slowly, and has a lot of code. Plus it uses looping.

This is a better way:
--********************************
WITH mycte AS
(
SELECT cast('1900-01-01' as datetime) DateValue
UNION ALL
SELECT DateValue + 1
FROM mycte
WHERE DateValue + 1 < '2050-12-31'

)
SELECT DateValue
FROM mycte
OPTION (MAXRECURSION 0)
--********************************

By default, the MAXRECURSION option is set to 100. I set it to zero, which means we could go into an infinite loop. I use the WHERE clause to make sure the loop bails out at 12/31/2050. Without the WHERE clause, we'd be hosed.

No comments:

Post a Comment