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

Do you want to insert more than one row? In which column are the two values (152,153) supposed to go? — user330315
– user330315, Commented Jan 21, 2012 at 14:03
Yes, multiple rows. I should have specified that. So in the end it should look like the edit above — JoeW
– JoeW, Commented Jan 21, 2012 at 14:24

user330315 · Accepted Answer · 2012-01-21 14:26:01Z

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

answered Jan 21, 2012 at 14:26

user330315

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

Comments

tpolyak · Accepted Answer · 2012-01-21 13:52:29Z

1

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

answered Jan 21, 2012 at 13:52

tpolyak

1,2391 gold badge9 silver badges15 bronze badges

Comments

Ali · Accepted Answer · 2012-01-21 13:57:43Z

0

Try this:

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

answered Jan 21, 2012 at 13:57

Ali

1,6882 gold badges27 silver badges49 bronze badges

Collectives™ on Stack Overflow

Batch database table data insert using MySQL

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related