Pete Freitag Pete Freitag

Multiple Inserts with MySQL

Updated on December 06, 2023
By Pete Freitag
databases

Andy Jarrett posted this technique for inserting multiple rows (bulk insert) in one SQL statement a few months ago. It's a handy trick:

INSERT INTO x (a,b)
VALUES 
 ('1', 'one'),
 ('2', 'two'),
 ('3', 'three')

I tried that on a couple hundred thousand rows today, and I got an error that my statement was bigger than the Max Allowed Packet size in MySQL. So keep that in mind when using. You can either change the setting, or go about it a different way.



mysql databases insert bulk sql

Multiple Inserts with MySQL was first published on June 10, 2005.

If you like reading about mysql, databases, insert, bulk, or sql then you might also like:

Discuss / Follow me on Twitter ↯

Comments

This is all good and well if you are inserting the exact same information into multiple tables, but what if you want to insert into 2 or more tables the information from ONE form, but each table has different sets of information. You have to then do it as 2 or more seperate INSERTS :(
by Mr K on 06/10/2005 at 5:57:42 PM UTC
re: how do you insert data from one record's ..?

using inner join... its helps vour u
by nurez on 07/24/2007 at 10:53:09 PM UTC
OK, so how can I do this with CFQUERY? When working with MySQL (PhpMyAdmin), this works fine--> [ INSERT into table (colA, colB, colC, colD) VALUES (1958, 106165412, 'stuff', 1), (1958, 106165412, 'stuff', 1), (1958, 106165412, 'stuff', 1), (1958, 106165412, 'stuff', 1), (1958, 106165412, 'stuff', 1) ]
But if I wrap it in CFQUERY, ColdFusion (BlueDragon) gives an error. Why?
by Derrick on 10/05/2007 at 4:55:40 PM UTC
I want to insert data from one table to other table. Both the table columns are different. Help me out.
by purnima on 04/03/2009 at 12:03:00 AM UTC
salam friendz
any one can help me in inserting data into more than one tables through a single insert query
by khadim on 10/05/2010 at 6:05:22 PM UTC
Thank you sir.
I have used this query to insert multiple record.
There is still a question.
If there are lac of record insert in same query. will there any problem with the query.
or we have to split the query with thousands.

also I want a progress that it will show how much record inserted.
like 2 record inserted the 3,4,5 and so on. is it possible

Thanks in advance.
by Som Darshan on 01/11/2011 at 3:04:06 AM UTC