Why do we have to make a SELECT after an 'INSERT ALL'?

Question

As I seen on many sites, if I want to make an INSERT ALL, I have to finish it with a SELECT (Like SELECT * FROM dual;)

Why?

Answer 1

A subquery is mandatory per the syntax of INSERT ALL (see http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_9014.htm#i2111652)

the insert clause is executed for each row returned by the subquery (i.e. the SELECT statement). SELECT * FROM dual returns a single row, so the insert_clause(s) is executed once (which is useful when you want to insert a hardcoded set of values)

Answer 2

Hi Acibi,

As shown in the documentation, the INSERT ALL synthax expects a subquery: you can not have INSERT ALL [...] VALUES [...].

I suspect the SELECT from dual is a way to make a multi-insert of one row on several tables.