CODEWITHSUNDEEP
mysqldatabaseinsertmysql-error-1064bulkinsert
JoeW
JoeW

Reputation: 588

Batch database table data insert using MySQL

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!)

Upvotes: 0

Views: 280

Answers (3)

user330315
user330315

Reputation:

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);

Upvotes: 1

Ali
Ali

Reputation: 1678

Try this:

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

Upvotes: 0

tpolyak
tpolyak

Reputation: 1239

After VALUES you also have to put groups of 4 values if you defined 4 columns.

Upvotes: 1

Related Questions