Insert a Record for Every Date Between Two Dates

Revisiting the Common Table Expression from an earlier post, I had already forgotten how to do this thing I actually ended up using this for, which was populating an “AllDates” table for my database. It turned out to be a lot easier to do this and then JOIN to it than to incorporate the CTE code into a larger query. So, here’s how to turn the CTE from the earlier post into an insert statement:

GO
/****** Object: Table [dbo].[allDates] Script Date: 03/27/2008 13:34:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[allDates](
[thedate] [datetime] NOT NULL,
CONSTRAINT [PK_allDates] PRIMARY KEY CLUSTERED
(
[thedate] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

WITH datecte(anydate) AS (SELECT CAST(‘1/1/2000’ AS datetime) AS anydate
UNION ALL
SELECT anydate + 1 AS anydate
FROM datecte AS datecte_1
WHERE (anydate < CAST('1/1/2021' AS datetime) - 1)) INSERT INTO allDates SELECT anydate FROM datecte AS datecte_2 OPTION (MAXRECURSION 10000)

Leave a Reply

Your email address will not be published. Required fields are marked *