Load database entries using a SELECT ... INSERT

Question

For the following statement:

INSERT INTO main_app_provider (provider) 
VALUES ((SELECT distinct(provider) FROM raw_financials));

I get a "subquery returns more than one row".

It seems the above is trying to do an INSERT into a single row. How would I load this into a table for multiple rows (i.e., so each provider is inserted into a new row)?

For reference, the MySQL manual shows the proper syntax for INSERT ... SELECT and all other statements MySQL supports. — outis
– outis, Commented Jan 23, 2012 at 19:32

Michael Berkowski · Accepted Answer · 2012-01-23 19:38:49Z

5

Remove the VALUES keyword. The parentheses around the SELECT are not strictly needed either.

INSERT INTO main_app_provider (provider) SELECT distinct provider FROM raw_financials;

See the MySQL INSERT syntax reference for full details.

edited Jan 23, 2012 at 19:38

answered Jan 23, 2012 at 19:30

Michael Berkowski

271k47 gold badges450 silver badges395 bronze badges

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

Comments

dani herrera · Accepted Answer · 2012-01-23 19:31:03Z

1

Right syntax is without values:

INSERT INTO main_app_provider (provider) 
  SELECT distinct(provider) 
  FROM raw_financials

answered Jan 23, 2012 at 19:31

dani herrera

52k11 gold badges133 silver badges188 bronze badges

Comments

Ashwin Chandran · Accepted Answer · 2012-01-23 19:32:04Z

0

Try this.

INSERT INTO main_app_provider (provider) SELECT distinct(provider) as Provider FROM raw_financials;

answered Jan 23, 2012 at 19:32

Ashwin Chandran

1,46714 silver badges11 bronze badges

Collectives™ on Stack Overflow

Load database entries using a SELECT ... INSERT

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related