Inserting multiple rows in 1 query mysql

Question

I've searched about this already on stackoverflow and found this: Inserting multiple rows in mysql

Sadly when I try this it's not working for me.

when I got a query like this:

INSERT INTO productielijn1
(Datum, Productieomschrijving, Aantal, AantalGemaakt, Verschil)
VALUES
('2016-01-12', '2016-01-12'),
('test1', 'test2'),
(1, 2),
(0, 0),
(0, 0);

it gives me the following error:

#1136 - Column count doesn't match value count at row 1

even when I add the () arround my values like this:

INSERT INTO productielijn1
(Datum, Productieomschrijving, Aantal, AantalGemaakt, Verschil)
VALUES
(('2016-01-12', '2016-01-12'),
('test1', 'test2'),
(1, 2),
(0, 0),
(0, 0));

it gives me the following error:

#1241 - Operand should contain 1 column(s)

So how can I solve this problem and add multiple lines with 1 query?

Seems pretty self-explanatory. You specified 5 columns but only supplied 2 values per row. — shmosel
– shmosel, Commented Jan 12, 2016 at 8:59
Oh right, it works the other way arround... I thought I needed to add each value 2 times (if I wanted to add 2 lines). My query isn't build up correctly. — Bart88
– Bart88, Commented Jan 12, 2016 at 9:01

Jens · Accepted Answer · 2016-01-12 08:59:10Z

3

Change to:

INSERT INTO productielijn1
(Datum, Productieomschrijving, Aantal, AantalGemaakt, Verschil)
VALUES
('2016-01-12', 'test1', 1,0,0),
('2016-01-12', 'test2',2,0,0);

You have to add the values row wise.

answered Jan 12, 2016 at 8:59

Jens

69.6k15 gold badges107 silver badges133 bronze badges

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

Comments

Gouda Elalfy · Accepted Answer · 2016-01-12 08:57:54Z

1

you insert 2 values in more than 2 column, your query can be:

INSERT INTO productielijn1
(Datum, Productieomschrijving)
VALUES
(('2016-01-12', '2016-01-12'),
('test1', 'test2'),
(1, 2),
(0, 0),
(0, 0));

or:

INSERT INTO productielijn1
(Datum, Productieomschrijving, Aantal, AantalGemaakt, Verschil)
VALUES
(('2016-01-12', '2016-01-12', '', '', ''),
('test1', 'test2', '', '', ''),
(1, 2, '', '', ''),
(0, 0, '', '', ''),
(0, 0, '', '', ''));

answered Jan 12, 2016 at 8:57

Gouda Elalfy

7,0833 gold badges28 silver badges38 bronze badges

Comments

Pupil · Accepted Answer · 2016-01-12 08:59:21Z

0

In your query:

INSERT INTO productielijn1
(Datum, Productieomschrijving, Aantal, AantalGemaakt, Verschil)
VALUES
('2016-01-12', '2016-01-12'),
('test1', 'test2'),
(1, 2),
(0, 0),
(0, 0);

Fields list has 5 columns where as your values list has only two.

Either remove three from fields list or add three in the columns list.

Solution to this:

Add default values to three remaining value columns:

INSERT INTO productielijn1
(Datum, Productieomschrijving, Aantal, AantalGemaakt, Verschil)
VALUES
('2016-01-12', '2016-01-12', '', '', ''),
('test1', 'test2', '', '', ''),
(1, 2, '', '', ''),
(0, 0, '', '', ''),
(0, 0, '', '', ''),;

answered Jan 12, 2016 at 8:59

Pupil

24k6 gold badges49 silver badges71 bronze badges

Comments

n-dru · Accepted Answer · 2016-01-12 08:59:53Z

0

Each set of values should match the number of the columns, so if the remaining 3 fields are supposed to remain empty, just add those to the sets, or fill them up with corresponding values:

INSERT INTO productielijn1
(Datum, Productieomschrijving, Aantal, AantalGemaakt, Verschil)
VALUES
('2016-01-12', '2016-01-12','','',''),
('test1', 'test2','','',''),
(1, 2,'','',''),
(0, 0,'','',''),
(0, 0,'','','');

answered Jan 12, 2016 at 8:59

n-dru

9,4402 gold badges31 silver badges43 bronze badges

Collectives™ on Stack Overflow

Inserting multiple rows in 1 query mysql

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related