Oracle 23c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table existingtable (date_col) as
select sysdate - 5 * level from dual connect by level <= 20
20 rows affected

SELECT DISTINCT EXTRACT(MONTH FROM Date_col) Month, EXTRACT(YEAR FROM Date_col) Year FROM ExistingTable;


MONTH
YEAR


2
2024

1
2024

12
2023

11
2023


CREATE SEQUENCE AutoIncrementTimeID MINVALUE 1 START WITH 1 INCREMENT BY 1 CACHE 10;
CREATE TABLE DatesTable AS SELECT DISTINCT AutoIncrementTimeID.nextval TimeID, EXTRACT(MONTH FROM Date_col) Month, EXTRACT(YEAR FROM Date_col) Year FROM ExistingTable;
ORA-02287: sequence number not allowed here

CREATE TABLE DatesTable AS
SELECT AutoIncrementTimeID.nextval TimeID, Month, Year
FROM (
  SELECT DISTINCT EXTRACT(MONTH FROM Date_col) Month, EXTRACT(YEAR FROM Date_col) Year
  FROM ExistingTable
);
4 rows affected

select * from DatesTable


TIMEID
MONTH
YEAR


1
2
2024

2
1
2024

3
12
2023

4
11
2023


drop table DatesTable;
CREATE TABLE DatesTable (TimeID INTEGER NOT NULL, Month INTEGER NOT NULL, Year INTEGER NOT NULL, PRIMARY KEY(TimeID));
INSERT INTO DatesTable (TimeID, Month, Year) VALUES (AutoIncrementTimeID.nextval, SELECT DISTINCT EXTRACT(MONTH FROM Date_col), EXTRACT(YEAR FROM Date_col) FROM ExistingTable);
ORA-00936: missing expression

INSERT INTO DatesTable (TimeID, Month, Year)
SELECT AutoIncrementTimeID.nextval TimeID, Month, Year
FROM (
  SELECT DISTINCT EXTRACT(MONTH FROM Date_col) Month, EXTRACT(YEAR FROM Date_col) Year
  FROM ExistingTable
);
4 rows affected

select * from DatesTable


TIMEID
MONTH
YEAR


5
2
2024

6
1
2024

7
12
2023

8
11
2023


drop table DatesTable
drop sequence AutoIncrementTimeId
CREATE TABLE DatesTable (
  TimeID INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  Month INTEGER NOT NULL,
  Year INTEGER NOT NULL
);
-- note TimeID not listed - handled by identity column
INSERT INTO DatesTable (Month, Year)
SELECT DISTINCT EXTRACT(MONTH FROM Date_col), EXTRACT(YEAR FROM Date_col) FROM ExistingTable;
4 rows affected

select * from DatesTable


TIMEID
MONTH
YEAR


1
2
2024

2
1
2024

3
12
2023

4
11
2023


drop table DatesTable
CREATE TABLE DatesTable (TimeID INTEGER NOT NULL, Month INTEGER NOT NULL, Year INTEGER NOT NULL, PRIMARY KEY(TimeID));
INSERT INTO DatesTable (TimeID, Month, Year)
SELECT ROW_NUMBER() OVER (ORDER BY Year, Month) TimeID, Month, Year
FROM (
  SELECT DISTINCT EXTRACT(MONTH FROM Date_col) Month, EXTRACT(YEAR FROM Date_col) Year
  FROM ExistingTable
);
4 rows affected

select * from DatesTable


TIMEID
MONTH
YEAR


1
11
2023

2
12
2023

3
1
2024

4
2
2024