How to insert into a table all dates and all months between year 2000 and 2020?

Question

I have created a table called time_range in SQL Server 2008 with columns

date ID, Month ID, year ID

How can I bulk insert all the date, month, year into these columns from year 2000 to 2020? Is there any simple query to do this?

Please help thanks in advance.

From:

1/1/2000  | January  |2000

TO :

31/12/2020| December | 2020

Answer 1

You can use recursive CTE to do this:

with cte (dateId)
as (
    select cast('2000-01-01' as date)

    union all

    select dateadd(day, 1, dateId)
    from cte
    where dateId < cast('2020-12-31' as date)
    )
select dateId,
    datename(Month, dateId) as monthId,
    year(dateId) as yearId
from cte 
option (maxrecursion 0);  -- to remove the recursion limit

You can use this to do the insert into other table