Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2009
    Posts
    2

    Inserting multiple rows at a time into table

    Hi,

    I am trying to implement functionality that will insert multiple rows at a time irrespective of the database used. The SQL command should be compatible across all the database. If any one knew about this please let me know as its very urgent.

    Thanks in advance
    Ravishankar

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    can't be done

    next question?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Do ya not reckon (for major RDBMSs anyway)?
    Pseudo code:
    Code:
    CREATE TABLE dummy (nowt INT)
    INSERT INTO dummy (nowt) VALUES(0)
    
    INSERT INTO destination (col1, col2, col3)
    SELECT 'this', 'that', 'the other'
    FROM dummy
    UNION
    SELECT 'something', 'nothing', 'anything'
    FROM dummy
    ..........
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    actually, for major database systems, just use this, it's standard SQL --
    Code:
    INSERT 
      INTO destination 
         ( col1, col2, col3 )
    VALUES
         ( 'this', 'that', 'the other' )
       , ( 'something', 'nothing', 'anything' )
    you might be surprised which microsoft database products would not be considered "major" by this criterion

    Edit: it's "F641, Row and table constructors" and i just realized it's optional -- apologies for the hasty snipe at ms


    heh -- i said "snipe"


    Last edited by r937; 03-11-09 at 09:51.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Mar 2009
    Posts
    2
    Will these SQL commands work for almost all databases like oracle,db2 and ms sql server

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, almost all of those

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Just so you know Rudy - SQL Server 2008 supports this construct
    George
    Home | Blog

  8. #8
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    PostgreSQL does as well.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    as does mysql

    the point wasn't to list the ones that do support row constructors, which is standard sql, but to highlight the "major" database systems that don't support it

    actually, the original question was whether there was a way to do it the same way across all database systems and the answer is still "no"

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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