Multiple Select statements into an Insert statement

Question

I've been searching for a while and I'm not sure if this is allowed in mysql.

I know this is allowed in mysql

Insert into 'table' (column1, column2, column3) Select val1, val2, val3 From sometable

Also this

Insert into 'table' (column1, column2, column3) Values (val1, val2, val3), (val1, val2, val3)

I am not sure if this is allowed though:

Insert into 'table' (column1, column2, column3) Select val1, val2, val3 From sometable, Select val1, val2, val3 From sometable

Obviously that gave me an error, is that allowed in mysql?

try select .... UNION ALL select ... just a hunch, never tried it — pala_
– pala_, Commented Apr 22, 2015 at 23:23

Gordon Linoff · Accepted Answer · 2015-04-22 23:24:37Z

2

You can try it, and it will fail. Use union all instead of ,:

Insert into table (column1, column2, column3)
    Select val1, val2, val3 From sometable
    union all
    Select val1, val2, val3 From sometable;

I assume the single quotes were there for some sort of effect, because you say that the first two queries actually work.

answered Apr 22, 2015 at 23:24

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

1 Answer 1