SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Zealot xPox's Avatar
    Join Date
    Sep 2005
    Posts
    124
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Multiple insert queries

    Is there a way to insert multiple rows into a table without having to use a Loop to repeat the Insert query?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    yes, there are two ways

    first, and most spectacularly, is to use row constructors, which are defined in standard SQL and supported by mysql!
    Code:
    insert
      into holidays
         ( year, holiday_name, holiday_date )
    values
         ( 2006, 'New Year''s Day', '2006-01-01' )
       , ( 2006, 'Good Friday',     '2006-04-15' )
       , ( 2006, 'Easter',          '2006-04-16' )
       , ( 2006, 'Victoria Day',    '2006-05-22' )
       ...
    the second method is to use multiple INSERT statements, but concatenate them with semi-colons:
    Code:
    insert
      into holidays
         ( year, holiday_name, holiday_date )
    values
         ( 2006, 'New Year''s Day', '2006-01-01' ) 
    ;
    insert
      into holidays
         ( year, holiday_name, holiday_date )
    values     
         ( 2006, 'Good Friday',     '2006-04-15' ) 
    ;
    insert
      into holidays
         ( year, holiday_name, holiday_date )
    values     
         ( 2006, 'Easter',          '2006-04-16' ) 
    ;
    insert
      into holidays
         ( year, holiday_name, holiday_date )
    values     
         ( 2006, 'Victoria Day',    '2006-05-22' ) ;
       ...
    you can pass multiple statements to the database in one call, but please note that this is not supported through ODBC
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot xPox's Avatar
    Join Date
    Sep 2005
    Posts
    124
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Up to 100 rows may be entered at one time. Is this too much for the query to handle using row constructors as suggested above?

  4. #4
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    100 rows is a small amount. nothing to worry about.

  5. #5
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    of course if you already have your data saved in a file you know you can use LOAD DATA INFILE to load the data even faster? You can insert millions of rows that way, no problem.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •