Batch database table data insert using MySQL

Question

I'm uploading multiple entries into a table in our database using MySQL. However, the following command does not work and throws up this error "#1136 - Column count doesn't match value count at row 1"... which is odd as there are 4 columns as included below:

(I've just included two of the results here as an example of the data)

INSERT INTO state (state_id,country_id,state_name,active) VALUES (152,153),(5),(Test1,Test2),(1)

This should form multiple rows and eventually look like this:

state_id | country_id | state_name | active 152 5 Test 1 1 153 5 Test 2 1

(that looks like a table in the edit... but not rendered on here, sorry!)

Answer 1

To insert multiple rows, you need to supply the correct number of values for each row.

The syntax is such that you specify one row after the other, not columns:

INSERT INTO state 
(state_id,country_id,state_name,active) 
VALUES 
(152,5,'Test1',1), 
(153,5,'Test2',1);