List:General Discussion« Previous MessageNext Message »
From:SGreen Date:March 31 2005 8:03pm
Subject:Re: How does a multi-row INSERT work?
View as plain text  
Dan Nelson <dnelson@stripped> wrote on 03/31/2005 03:01:45 PM:

> In the last episode (Mar 31), Chris W. Parker said:
> > I searched the archives, looked through the manual, and searched
> > google for info on how to actually perform a multi-row INSERT but
> > didn't find an answer.
> > 
> > Would someone please show me the syntax for this please?
> > 
> > I could just do a loop and INSERT the data that way but according to 
the
> > manual, a multi-row INSERT is faster.
> 
> The syntax is described in the manual, but not with an explicit
> example.  One of the user comments gives an exaple.
> http://dev.mysql.com/doc/mysql/en/insert.html
> 
> INSERT INTO mytable (f1,f2,f3) VALUES (1,2,3),(4,5,6),(7,8,9);
> 
> will insert three rows.  Just tack as many ,(...) clauses on the end as
> you want.
> 
> -- 
>    Dan Nelson
>    dnelson@stripped
> 

One warning they don't mention is that the longest statement you can make 
determined by the server's max_allowed_packet variable. You can add as 
many ,(...) sets into your statement as you like so long as you don't make 
a statement that's too big. To ask your server what it's value is, run 
this command:

SHOW VARIABLES LIKE 'max%';

max_allowed_packets will be one of the values listed.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Thread
How does a multi-row INSERT work?Chris W. Parker31 Mar
  • Re: How does a multi-row INSERT work?Jonathan Wright31 Mar
  • Re: How does a multi-row INSERT work?Dan Nelson31 Mar
    • Re: How does a multi-row INSERT work?SGreen31 Mar
  • Re: How does a multi-row INSERT work?John McCaskey31 Mar
  • Re: How does a multi-row INSERT work?Rhino1 Apr
  • Re: How does a multi-row INSERT work?Gabriel PREDA1 Apr
RE: How does a multi-row INSERT work?Chris W. Parker31 Mar
Re: How does a multi-row INSERT work?beacker31 Mar