CODEWITHSUNDEEP
mysqlsql
user2710234
user2710234

Reputation: 3225

INSERT values from one table to another using SQL Query

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

Upvotes: 0

Views: 786

Answers (2)

Zafar Malik
Zafar Malik

Reputation: 6854

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;

Upvotes: 0

Thorsten Dittmar
Thorsten Dittmar

Reputation: 56727

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.

Upvotes: 4

Related Questions