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.

1 comment:

  1. I have read news on technical sites that GiveBIG is now dominating the spring fundraising season for most nonprofits in every sector in Seattle and King County. The nonprofit community and fundraising thought leaders are trying to make sense of the GiveLocalAmerica kerfuffle. Great!!friv jogos online
    jogos online 2019
    friv jogos 4 school online

    ReplyDelete