Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2003
    Posts
    1,074

    Unanswered: multiple row insert, one SQL stmt

    So MySQL allows this syntax (multiple rows on an INSERT). Does Oracle have any equivalent?

    Code:
     INSERT INTO BookTable
                 (  
                     BookId , Title  , Description  
                 )  
             VALUES  
                 (                         
                     101, "Moby Dick", "Whale book";
                     101, "Beaches", "Chick book";
                     101, "From Good to Great", "Boss book";
                     101, "C#", "Nerd book"
                 )

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    insert ALL
        INTO NR_ATG_ORDER_REL VALUES ('o27290008', 0, 'order1001023')
        INTO NR_ATG_ORDER_REL VALUES ('o27400001', 0, 'order1001071')
        INTO NR_ATG_ORDER_REL VALUES ('o27400002', 0, 'order1001073')
        SELECT * FROM DUAL
    /
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Dec 2003
    Posts
    1,074
    Weird ... why is the "SELECT * FROM DUAL" required? I guess I got it also to work with

    Code:
    create table mult_ins (f1 number, f2 number);
    
    insert all 
      into mult_ins values (1,100)
      into mult_ins values (2,200)
      into mult_ins values (3,300)
      select * from mult_ins where rownum < 2
    but the SELECT at the end is befuddling me ...

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >why is the "SELECT * FROM DUAL" required?
    I did NOT state it was "required".
    I posted what I know works.
    If you don't like this solution, my feelings won't be hurt if you obtain a different one.
    Other, even better, solutions may exists, but it is not worth my time to try to find 1.

    With free advice, you get what you paid for it.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Dec 2003
    Posts
    1,074
    It was less of an antagonistic interrogation, than it was simple curiosity. The syntax seemed strange, is all. I was working from home & didn't have my Oracle bookmarks available to look it up in the documentation. Or at least, I didn't want to take the time to search for it via Google.

    I went ahead and sought it out anyway, since your guarded response made me even more curious, and after reading the docs it still strikes me as odd. I'll have to play with it a bit more until I get the hang of it, and figure out what INSERT ALL, in general, is really useful for.

    Thanks for showing me something new.
    --=Chuck

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the code in post #1 is wrong, you need more parentheses
    Code:
    INSERT 
      INTO BookTable
         ( BookId , Title , Description )  
    VALUES  
         ( 101, "Moby Dick", "Whale book" )
       , ( 102, "Beaches", "Chick book" )
       , ( 103, "From Good to Great", "Boss book" )
       , ( 104, "C#", "Nerd book" )
    also, there is nothing but pain and frustration waiting for you if you insert 4 different books with the same bookid

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

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by chuck_forbes
    Weird ... why is the "SELECT * FROM DUAL" required?
    The example above is an "unanticipated" use of Oracle's multi-table insert statement, which is really intended for use in transforming data. There are some good examples here in the Oracle SQL Reference.

Posting Permissions

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