Inserting a GROUP BY result into another table

Question

Hello,

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?

TIA

jg

Answer 1

You can try:

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

Answer 2

If I understand you correctly, you want something like:

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

Answer 3

INSERT INTO foo (fieldName1,fieldName2,fieldName3) 
    SELECT '',bar,'' FROM baz GROUP BY bar

Answer 4

Going with Michael's answer, another possibility would be

INSERT INTO foo (col2name) SELECT bar FROM baz GROUP BY bar

where col1 and col3 are defined to have a default of the empty string.

Answer 5

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