One query to insert multiple rows with multiple columns

Question

Let's say I want to insert this data:

Row 1: People = '40', Places = '15'
Row 2: People = '5', Places = '10'

I understand that this is how you'd execute the above:

mysql_query("INSERT INTO mytable(`People`, `Places`) 
VALUES ('40', '15'),('5', '10')");

But what if I wanted to insert into more than two columns with a single query? What if the data to be inserted was like this:

Row 1: People = '40', Places = '15'
Row 2: People = '5', Places = '10'
Row 3: Things = '140', Ideas = '20'
Row 4: People = '10', Things = '5', Ideas = '13'

I can't seem to find a question like this anywhere else.

I guess you cannot do this with a single query!

Anji
– Anji

2012-04-23 19:05:17 +00:00
Commented Apr 23, 2012 at 19:05 — Anji
– Anji, Commented Apr 23, 2012 at 19:05

juergen d · Accepted Answer · 2012-04-23 19:07:00Z

4

Leave the columns you don't wanto to fill with null

INSERT INTO mytable(`People`, `Places`, Things, Ideas) 
VALUES ('40', '15', null, null),(null, null, 100, 20)

answered Apr 23, 2012 at 19:07

juergen d

205k40 gold badges305 silver badges377 bronze badges

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

Comments

Developer · Accepted Answer · 2012-04-23 19:07:25Z

4

mysql_query("INSERT INTO mytable(`People`, `Places`, `Ideas`, `things`)
 VALUES ('40', '15',  null, null),
        (null, '5',   '10', null),
        ('10',  null, '11', '12')");

Or if you want to use 0 instead of null, it will likely be more friendly to you application (no throwing null errors)

mysql_query("INSERT INTO mytable(`People`, `Places`, `Ideas`, `things`)
 VALUES ('40', '15', '0',  '0'),
        ('0',  '5',  '10', '0'),
        ('10', '0',  '11', '12')");

answered Apr 23, 2012 at 19:07

Developer

2,00612 silver badges11 bronze badges

Comments

Jose Vega · Accepted Answer · 2012-04-23 19:08:58Z

1

INSERT INTO mytable(`People`, `Places`,`Things`,`Ideas`) 
VALUES ('40', '15', null, null),
       ('5', '10',null, null),
       (null, null, '140','20'),
       ('10',null,'5','13')");

answered Apr 23, 2012 at 19:08

Jose Vega

10.3k7 gold badges43 silver badges58 bronze badges

Comments

GDP · Accepted Answer · 2012-08-02 18:39:58Z

1

You could write separate query statements in one single line like this:

insert into table_x (collumn_x,collumn_y) values (... ; 
insert into table_x (collumn_y, collumn_z) values (...

and so on

the structure to mount the statement dinamically may be complex to build, but at least is the only solution i can figure out for u at the moment

hope this helps you

edited Aug 2, 2012 at 18:39

GDP

8,1636 gold badges48 silver badges84 bronze badges

answered Apr 23, 2012 at 19:10

Eduardo

211 bronze badge

Collectives™ on Stack Overflow

One query to insert multiple rows with multiple columns

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