SQL & PL/SQL

Syntax for INSERT ALL -

INSERT ALL
INTO <table_name1> VALUES <column_name_list)
INTO <table_name2> VALUES <column_name_list)
...
<SELECT Statement>;

Yes, You can insert insert the way you want but it actually doesn't serve the purpose of using INSERT ALL.
This is primarily used if you want data to be inserted in multiple tables & not in a single table. (This is called a Multi-table Insert )

Refer -- http://www.oracle-developer.net/display.php?id=209

Refer --
http://en.wikipedia.org/wiki/DUAL_table
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1562813956388

Edited by: ranit B on Oct 23, 2012 12:53 AM

Hi,
Here's a different way that I find more convenient, mostly because the columns only have to be named once:

INSERT INTO EASY_DRINKS (DRINK_NAME,        MAIN,     AMOUNT1, SECOND,     AMOUNT2, DIRECTIONS) 
       	         SELECT 'Kiss on the Lips', 'cherry juice', 2, 'apricot nectar', 7, 'serve over ice with straw')
		 FROM dual
     UNION ALL	 SELECT 'Hot Gold',  	    'peach nectar', 3, 'orange juice', 	 6, 'pour hot orange juice in mug and add peach nectar'
		 FROM dual
...
     UNION ALL	 SELECT 'Soda and It', 	    'soda', 	    2, 'grape juice', 	 1, 'shake in cocktail glass, no ice')
		 FROM dual
;

SELECT ALL is great when you need to insert the same data into multiple tables. That doesn't mean you can't use it to insert multiple rows into the same table, it just means that there's no reason to try it first.
Dual behaves just like other tables.
Since DUAL has only one column, called DUMMY, "SELECT * FROM dual" is equivalent to "SELECT dummy FROM dual". That would produce a row that has the value of DUMMY, which happens to be 'X'. If you wanted to insert a capital 'X', then "SELECT * FRom DUAL" would do just what you wanted. But if you want the numebr 1, then you have to SELECT the number 1.

Edited by: Frank Kulash on Oct 22, 2012 4:01 PM
Added "FROM dual"

Thank you for the responses guys - helpful stuff.

@Frank:

The following code as you mentioned did not work either:

INSERT ALL INTO EASY_DRINKS (drink_name, main, amount1, second, amount2, directions)
SELECT ('3Blackthorn', 'tonic water', 1.5, 'pineapple juice', 1, 'stir with ice, strain into cocktail glass with lemon twist')
UNION ALL SELECT ('3Blue Moon', 'soda', 1.5, 'blueberry juice', .75, 'stir with ice, strain into cocktail glass with lemon twist');

-----------------------------
Error starting at line 1 in command:
INSERT ALL INTO EASY_DRINKS (drink_name, main, amount1, second, amount2, directions)
SELECT ('3Blackthorn', 'tonic water', 1.5, 'pineapple juice', 1, 'stir with ice, strain into cocktail glass with lemon twist')
UNION ALL SELECT ('3Blue Moon', 'soda', 1.5, 'blueberry juice', .75, 'stir with ice, strain into cocktail glass with lemon twist')
Error at Command Line:2 Column:24
Error report:
SQL Error: ORA-00907: missing right parenthesis
00907. 00000 - "missing right parenthesis"
*Cause:
*Action:

Hi, "Missing right parenthesis" is a generic error message, that can be caused by any number of different syntax errors. Ironically, here it's caused by having too many parentheses, not by missing some.

Lose the parentheses in the SELECT statements. See my example.
You only need parenthese around the list of column names.

I corrected my previous message, adding "FROM dual" to all the SELECT statements.

Got it, thank you - So I will have to specify from DUAL for every row in the end and UNION ALL in the beginning? Thanks Frank.

Hi, There's no UNION ALL before the first SELECT keyword. Aside from that, you're right: you have to repeat UNION ALL and FROM DUAL for every row you're adding. It's better than having to repeat the list of column names.

There are other ways, but all the ones I know are just as bad in some respects, and worse in others.