One query to insert multiple rows with multiple columns
11,564
Solution 1
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)
Solution 2
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')");
Solution 3
INSERT INTO mytable(`People`, `Places`,`Things`,`Ideas`)
VALUES ('40', '15', null, null),
('5', '10',null, null),
(null, null, '140','20'),
('10',null,'5','13')");
Solution 4
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
Author by
Norse
Updated on June 04, 2022Comments
-
Norse almost 2 years
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.