Dan Nelson <[EMAIL PROTECTED]> 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 > [EMAIL PROTECTED] >
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
