[chuck_forbes]

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

 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"
             )  

[anacedent]

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
/

[chuck_forbes]

Weird ... why is the "SELECT * FROM DUAL" required? I guess I got it also to work with

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 ...

[anacedent]

>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.

[chuck_forbes]

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

[r937]

the code in post #1 is wrong, you need more parentheses

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

[andrewst]

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.