INSERT values from one table to another using SQL Query

Question

I am trying to run this SQL Query:

INSERT INTO controldata (field,value) 
VALUES (customer_billing_product_type, 
  SELECT name from customer_billing_product_types)

to insert all rows in the customer_billing_product_types table into the controldata table with the field always being customer_billing_product_type but i get an SQL Error saying:

#1064 - You have an error in your SQL syntax; 
check the manual that corresponds to your MySQL server version for the right syntax to use near 
'SELECT name from customer_billing_product_types)' at line 1

Writing each SQL instruction (e.g. INSERT INTO, SELECT, etc.) below one another helps greatly in solving syntax errors like these. — alex
– alex, Commented Apr 30, 2014 at 7:47

Thorsten Dittmar · Accepted Answer · 2014-04-30 07:45:02Z

4

The INSERT as you wrote it expects to insert one record, but then in values an entire set of records is returned. Try to change your query to

INSERT INTO controldata(field, value)
SELECT customer_billing_product_type, t.name FROM customer_billing_product_types t;

This selects all t.name values from customer_billing_product_types, adds customer_billing_product_type as column and inserts all the results into controldata.

answered Apr 30, 2014 at 7:45

Thorsten Dittmar

56.9k10 gold badges95 silver badges145 bronze badges

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

Comments

Zafar Malik · Accepted Answer · 2014-04-30 07:51:11Z

0

You are using wrong syntax try below:

INSERT into controldata (field1) SELECT name from customer_billing_product_types;

If you want to keep a field as constant then you can use as per below:

INSERT into controldata (field,value) SELECT 'customer_billing_product_types',name from customer_billing_product_types;

edited Apr 30, 2014 at 7:51

answered Apr 30, 2014 at 7:45

Zafar Malik

6,8723 gold badges23 silver badges31 bronze badges

1 Comment

Thorsten Dittmar Over a year ago

You forgot the constant field.

Collectives™ on Stack Overflow

INSERT values from one table to another using SQL Query

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related