How to do a batch insert in MySQL

mysql sql database insert
Genadinik picture Genadinik · Apr 3, 2011 · Viewed 269.4k times · Source

I have 1-many number of records that need to be entered into a table. What is the best way to do this in a query? Should I just make a loop and insert one record per iteration? Or is there a better way?

Answer

nietaki picture nietaki · Apr 3, 2011

From the MySQL manual

INSERT statements that use VALUES syntax can insert multiple rows. To do this, include multiple lists of column values, each enclosed within parentheses and separated by commas. Example:

INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);

Related questions

How do I get the ID of multiple inserted rows in MySQL?

I am inserting some words into a two-column table with this command: INSERT IGNORE INTO terms (term) VALUES ('word1'), ('word2'), ('word3'); How can I get the ID (Primary Key) of the row in which each word is …

Read More
MySQL 5.6 Error 1054: Unknown column in 'field list' on INSERT

I'm using MySQL Workbench to create and manage a comic book store database. I'm having a problem inserting rows into the COMIC_ISSUE table. The problem I'm having is the same problem many others posted questions about here only none …

Read More
'IF' in 'SELECT' statement - choose output value based on column values

SELECT id, amount FROM report I need amount to be amount if report.type='P' and -amount if report.type='N'. How do I add this to the above query?

Read More