Insert records for 40years in table only date-CodePudding

I need to insert only date into a table MONTH_YEAR from 01-01-2010 to 01-01-2040:

For example, I need to insert record on my table month wise DATE:

01-01-2010
01-02-2010
01-03-2010
01-04-2010
01-05-2010
01-06-2010
01-07-2010
01-08-2010
01-09-2010
01-10-2010
01-11-2010
01-12-2010
01-01-2011
01-02-2011
01-03-2011
01-04-2011
01-05-2011
.....................................

01-06-2040
01-07-2040
01-08-2040
01-09-2040
01-10-2040
01-11-2040
01-12-2040

Like this I want to insert only date into my table for month wise from 01-01-2010 to 01-01-2040

CodePudding user response：

Row generator it is:

SQL> insert into month_year (datum)
  2  select date '2010-01-01'   level - 1
  3  from dual
  4  connect by level <= date '2040-01-01' - date '2010-01-01'   1;

10958 rows created.

SQL> select min(datum) min_date,
  2         max(datum) max_date
  3  from month_year;

MIN_DATE   MAX_DATE
---------- ----------
01.01.2010 01.01.2040

SQL>

If you only need 1st of every month, then

SQL> insert into month_year (datum)
  2  select add_months(date '2010-01-01', level - 1)
  3  from dual
  4  connect by level <= months_between(date '2040-01-01', date '2010-01-01')   1;

361 rows created.

SQL>

CodePudding user response：

You can use a hierarchical query:

INSERT INTO month_year (column_name)
SELECT ADD_MONTHS(DATE '2010-01-01', LEVEL - 1)
FROM   DUAL
CONNECT BY LEVEL <= 31*12;

Or a recursive query:

INSERT INTO month_year (column_name)
WITH range (dt) AS (
  SELECT DATE '2010-01-01' FROM DUAL
UNION ALL
  SELECT ADD_MONTHS(dt, 1)
  FROM   range
  WHERE  dt < DATE '2040-12-01'
)
SELECT dt FROM range;

db<>fiddle here