Multiple Inserts with MySQL
June 10, 2005
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.
Tweet
add to del.icio.usRelated Entries
- Insert Delayed with MySQL - August 2, 2005
- Sphinx - Open Source SQL Full Text Search Engine - November 1, 2006
- Updated SQL Reserved Words Checker - March 28, 2006
- MySQL FULLTEXT Indexing and Searching - September 29, 2005
- SQL to Select a random row from a database table - September 14, 2005
Trackbacks
Trackback Address: 379/0D1EEEAD85EC4EA978503A0A6E9D6632
Comments
On 06/10/2005 at 7:57:42 PM EDT 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 :(
On 04/11/2006 at 2:08:39 AM EDT sarah wrote:
2
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.
On 07/25/2007 at 12:53:09 AM EDT nurez wrote:
3
re: how do you insert data from one record's ..?
using inner join... its helps vour u
On 08/31/2007 at 7:16:53 AM EDT Rajesh wrote:
4
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..
On 09/26/2007 at 7:09:12 PM EDT kendall wrote:
5
you could use transactions to insert in to multiple tables.
On 10/05/2007 at 6:55:40 PM EDT Derrick wrote:
6
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?
On 11/30/2007 at 3:43:51 PM EST Dan wrote:
7
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 :(
On 02/23/2008 at 9:35:57 AM EST kuchnie wrote:
8
http://www.petefreitag.com/item/379.cfm zajbiste wogole nie ma co elooo
On 02/23/2008 at 9:40:15 AM EST mezszczyzna wrote:
9
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
On 04/03/2009 at 2:03:00 AM EDT purnima wrote:
10
I want to insert data from one table to other table. Both the table columns are different. Help me out.
On 12/22/2009 at 8:06:11 AM EST www.homequran.com wrote:
11
This is an excellent post.
On 08/14/2010 at 5:46:29 AM EDT arthur wrote:
12
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/
Post a Comment
Recent Entries
- Howto Install and Run the Android Emulator
- jQuery UI Autocomple IE 6 Select List z-Index Issues
- Path Traversal Vulnerability Security Hotfix for ColdFusion Released
- Using AntiSamy with ColdFusion
- Writing Secure CFML Slides from CFUnited 2010
- Locking Down ColdFusion Presentation Slides
- Cross Domain Data Theft using CSS
- Using jQuery UI Autocomplete with Hidden ID's
using inner join... its helps vour u
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 :(
