SQL Insert Into multiple rows, but execute subquery only once

Question

Im trying to optimize an INSERT INTO statement to use a subquery only once, as it is always the same value:

Heres my example code

INSERT INTO TABLE1 (id, number) VALUES 
((SELECT other_id from TABLE2 WHERE somevalue = "test"), 12),
((SELECT other_id from TABLE2 WHERE somevalue = "test"), 13),
...,
...;

Not an sql-expert, but this doesnt look like a good approach, as the same subquery gets executed on every insert.

Is there an alternative solution?

also, i know i can select the ID beforehand and store it in a variable like this (pseudo-code-like):

$var = mysql_query("SELECT other_id from TABLE2 WHERE somevalue = 'test'")
 mysql_query("INSERT INTO TABLE1 (id, number) VALUES 
    ($var, 12),
    ($var, 13);")

Quassnoi · Accepted Answer · 2014-06-24 13:21:49Z

2

INSERT
INTO    table1 (id, number)
SELECT  other_id, number
FROM    table2
CROSS JOIN
        (
        SELECT  12 number
        UNION ALL
        SELECT  13
        ) q
WHERE   somevalue = 'test'

answered Jun 24, 2014 at 13:21

Quassnoi

427k94 gold badges628 silver badges623 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Wolf Over a year ago

what if there are more than just 2 values? wouldn't i have to create a huge join table?

Kickstart Over a year ago

Yes, but those should be quick (as you would just be selecting constants and unioning the results together), while the actual query against data is just performed once. If those numbers are stored in a table (such as if they are the keys to records on another table) then you could join against that table using IN(13,13) instead of unioning the constants.

Collectives™ on Stack Overflow

SQL Insert Into multiple rows, but execute subquery only once

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related