Insert records into one table using key from another table

I have a table with year, start and end columns like below. It has several years in it. Do not ask me why there is a year called 4 in there because I could not tell you. I just know that it cannot be removed from the table or else the web application will break.

year    start       end
2019    2019-01-01  2019-12-31
4       2019-04-01  2020-03-31

I use this query to insert records into another table.

INSERT INTO table2 (id, other, medicaid, commercial, year)
SELECT id, other, 
medicaid, commercial, 
4 as year 
FROM crosstab_table

As you can see 4 is hardcoded in the query. How do I change this query to insert records for all years that are in table1? For example, how do I convert the two queries below into one query using the table with the years in it.

INSERT INTO table2 (id, other, medicaid, commercial, year)
SELECT id, other, 
medicaid, commercial, 
4 as year 
FROM crosstab_table;

INSERT INTO table2 (id, other, medicaid, commercial, year)
SELECT id, other, 
medicaid, commercial, 
2019 as year 
FROM crosstab_table

Answer

demo:db<>fiddle

You could use a CROSS JOIN:

INSERT INTO table2 (id, other, medicaid, commercial, year)
SELECT 
    ct.id, ct.other, ct.medicaid, ct.commercial, 
    s.year
FROM crosstab_table ct
CROSS JOIN (
    SELECT year FROM table1
) s;

Advertisement

Answer