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.
x
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
Advertisement
Answer
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;