Can you INSERT SELECT multiple rows without running select on the same row each time?

Question

If I have a table and I need a value from another value and the other values ​​come from somewhere else, do I have to run the select every time?

INSERT INTO table1 (name, otherValue) VALUES 
(SELECT name FROM table2 WHERE id = 1, outsideValue1),
(SELECT name FROM table2 WHERE id = 1, outsideValue2),
(SELECT name FROM table2 WHERE id = 1, outsideValue3);

So the name is the same and otherValue is different every time I try to batch insert.

Answer 1

One way is to put the "other values" into a derived table to cross-join with a single source record:

INSERT INTO table1 (name, otherValue)
SELECT t2.name, v.val
FROM table2 t2
CROSS JOIN (
    SELECT 'val1' as val
    UNION ALL SELECT 'val2'
    UNION ALL SELECT 'val3'
) v
WHERE t2.id = 1

In MySQL 8.0.19 and above, we can use VALUES/ROW syntax:

INSERT INTO table1 (name, otherValue)
SELECT t2.name, v.val
FROM table2 t2
CROSS JOIN (VALUES 
    ROW('val1'),
    ROW('val2'),
    ROW('val3')
) v(val)
WHERE t2.id = 1