insert multiple rows that are generated by multiple selects

47 views Asked by At

I have 2 tables. One of which has a list of names and the other consists of specific dates. I need to be able to generate an insert function that will insert rows into the third table.

For every row on the first table, i need to insert all the rows from the second table into the third table.

lets say there are 10 rows in table 1, 20 rows in table two. The insert function must be able to insert 200 rows.

so if i have a table with Mary, John and James on the names column and another table with 2017-01.01, 2017-05-01, 2017-08-08, 2016-12-31 on the date column. I need an insert function that would be able to insert rows into the third table so that there would be all those rows of dates in date column with Mary in the name column, all those dates with John in the name column etc.

the reason is that I actually have ca 400 names on name column and ca 50 dates on the dates column (depending on the exact select). I would hate to have to do this manually.

I would google it but I'm having hard time coming up with the correct search term. I'm getting the answers I am not looking for...

2

There are 2 answers

1
Remco On BEST ANSWER

This query should get you the same result, the cross is not required.

INSERT INTO table3 (name, date)
SELECT table1.name, table2.date
FROM table1, table2;
1
Gordon Linoff On

You are looking for a cross join, I think:

insert into table3 (name, date)
    select t1.name, t2.date
    from table1 t1 cross join
         table2 t2;