Multiple Inserts with MySQL

June 10, 2005
databases

Andy Jarrett posted this technique for insterting 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.



Related Entries

12 people found this page useful, what do you think?

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 :(
hi, how do you insert data from one record's column into another record's column in the same table. p.s the columns from where and to where data is being transfered is the same column in the same table.
re: how do you insert data from one record's ..? using inner join... its helps vour u
God bless Andy!By far, this has been the best tip that someone ever gave in a forum. It greatly reduced my burden of inserting 10 Mil rows..
you could use transactions to insert in to multiple tables.
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?
You would have to anyway. RE: On 06/10/2005 at 5:57:42 PM MDT Mr K wrote: 1 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 :(
http://www.petefreitag.com/item/379.cfm zajbiste wogole nie ma co elooo
My problem is similar to above, please look here http://bling-bling.pl in comments section where im adding comment throght ajax to multiple tables
I want to insert data from one table to other table. Both the table columns are different. Help me out.
This is an excellent post.
I am trying to do mass insert on directory MySQL db and such script timeouts. I have set the timeout to 0 (unlimited) in php.ini and not running insafe mode.
After I restart it spits out duplicate data errors.
What might be the issue?
This is the site I am talking about http://quotes.insurance-forums.net/
salam friendz
any one can help me in inserting data into more than one tables through a single insert query
Nice. didn't use this solution until now.
Mainly did it with "," seperator.
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.

Post a Comment




  



Spell Checker by Foundeo

Recent Entries



foundeo


did you hack my cf?