Search code examples
mysqlsqldomo

Insert Multiple Rows from Table A to Table B on Multiple Different Dates MYSQL

I have Table A with a list of dates

DATE
Aug 20, 2021
Aug 21, 2021
Aug 22, 2021

And Table B with Variables

LOCATION VARIABLE
A 100
B 200

How would I insert both the locations and variables for each date like this?:

Date LOCATION VARIABLE
Aug 20, 2021 A 100
Aug 20, 2021 B 200
Aug 21, 2021 A 100
Aug 21, 2021 B 200
Aug 22, 2021 A 100
Aug 22, 2021 B 200

Table A is updated Daily while Table B is Static,

Thanks!

Solution

  • Use a cross join:

    SELECT a.DATE, b.LOCATION, b.VARIABLE
FROM TableA a
CROSS JOIN TableB b;

    If you actually want to insert this data into another table, then use an INSERT INTO ... SELECT, using the above select query.