Inserting a GROUP BY result into another table

Question

I was migrating a field to a new table. The new table has three fields. What I tried was

INSERT INTO foo VALUES ('', (SELECT bar FROM baz GROUP BY bar), '');

This resulted in an error due to multiple rows resulting from the select.

What is the right way to do this?

Answer 1

If I understand you correctly, you want something like:

INSERT INTO foo (col1name, col2name, col3name)
    SELECT '', bar, ''
    FROM baz
    GROUP BY bar

Answer 2

Or if I'm understanding you correctly and you want one entry in the new table for every distinct bar value in the old table I think this makes that a bit clearer.

INSERT INTO foo (col2name) SELECT DISTINCT bar FROM baz

The execution plan and performance should be similiar

Answer 3

You can try:

INSERT INTO foo 
SELECT '',bar,'' FROM baz GROUP BY bar