Paste INTO MySQL from another table

Question

INSERT INTO campaign_ledger (`campaign_id`, `description`, amount, balance, timestamp) VALUES (SELECT id as campaign_id, 'Ported from campaigns' as description, budget_remaining as amount, budget_remaining as balance, NOW() as timestamp FROM campaigns) 

This is my syntax, but I get an error:

You have an error in the SQL syntax; check the manual that matches your version of MySQL server for the correct syntax to use next to 'SELECT id as campaign_id,' Ported from campaigns 'as description, budget_remaini' on line 2

What am I doing wrong?

Answer 1

Since you are selecting from a table, you need to use the INSERT INTO SELECT FROM query:

 INSERT INTO campaign_ledger ( `campaign_id` , `description` , amount , balance , timestamp ) SELECT id as campaign_id , 'Ported from campaigns' as description , budget_remaining as amount , budget_remaining as balance , NOW() as timestamp FROM campaigns 

Use only INSERT INTO VALUES when you are using certain values ​​and not selecting from a table. If you want to use INSERT INTO VALUES , then your query will look like this:

 INSERT INTO campaign_ledger ( `campaign_id` , `description` , amount , balance , timestamp ) VALUES ( 1 , 'test' , 100.00 , 1000.00 , NOW() ) 

Answer 2

 INSERT INTO campaign_ledger (`campaign_id`, `description`, amount, balance, timestamp) SELECT id as campaign_id, 'Ported from campaigns' as description, budget_remaining as amount,budget_remaining as balance, NOW() as timestamp FROM campaigns; 

Answer 3

Part of the VALUES request is not required. For instance:

  INSERT INTO campaign_ledger (`campaign_id`, `description`, amount, balance, timestamp) SELECT id as campaign_id, 'Ported from campaigns' as description, budget_remaining as amount, budget_remaining as balance, NOW() as timestamp FROM campaigns;