MySQL How do you INSERT INTO a table with a SELECT subquery returning multiple rows?

Question

MySQL How do you INSERT INTO a table with a SELECT subquery returning multiple rows?

  INSERT INTO Results     (      People,      names,     )     VALUES     (      (        SELECT d.id        FROM Names f        JOIN People d ON d.id  = f.id      ),       (       "Henry"      ),     ); 

I WANT to populate the new table with all results returning from this subquery. How do I do this without getting a ERROR 1242 (21000): Subquery returns more than 1 row

Answer 1

INSERT INTO Results (People, names )    SELECT d.id, 'Henry'    FROM Names f    JOIN People d ON d.id  = f.id 

Combine the static string Henry with your SELECT query.

Answer 2

Here is what I've found that works well. It is a little long but many times extra data needs to be shuffled around.

Insert multiple rows into table1 from table2 with values. EXAMPLES:

INSERT INTO table1 (col1, col2, col3, col4, col5)  SELECT col1,col2,col3,col4,col5  FROM table2 t2  WHERE t2.val2 IN (MULTIPLE VALUES)  AND (Another Conditional); 

You can insert hard coded values to get insert multiple rows with repeat data:

INSERT INTO table1 (col1, col2, col3, col4, col5)  SELECT "Value", col2, col3, "1900-01-01","9999-12-31"  FROM table2 t2  WHERE t2.val2 IN (MULTIPLE VALUES)  AND (Another Conditional); 

Note that: "Value","1900-01-01","9999-12-31" will repeat across all rows inserted.