This discussion is archived
3 Replies Latest reply: May 3, 2012 4:08 AM by 706771 RSS

way INSERT ALL works

706771 Newbie
Currently Being Moderated
Dear Members :

If I use the following:

insert all into emp1 (empno,ename,deptno,sal) values (333,'anthony', 10, 50000)
into emp2 (empno,ename,deptno,sal) values (333,'anthony', 10, 50000)
Select empno,ename,deptno,sal from emp;

14 rows will be inserted into each emp1 and emp2 with values 333,....

but if I use :

insert all into emp1 (empno,ename,deptno,sal) values (333,'anthony', 10, 50000)
into emp2 (empno,ename,deptno,sal) values (333,'anthony', 10, 50000)
Select * from dual;

just 1 row will be inserted into each emp1 and emp2, as expected.

So what's the mechanism of "INSERT ALL", I mean does it execute for each row the subquery returns ?

Thanks in advance.
Atanu
  • 1. Re: way INSERT ALL works
    Justin Cave Oracle ACE
    Currently Being Moderated
    Yes, INSERT ALL processes every row that the SELECT query returns.

    Normally, of course, if the SELECT statement is going to return more than 1 row, you would be inserting values that were returned by the SELECT queries into the specified table. Using a SELECT statement of
    SELECT *
      FROM dual
    is a special case for situations where you want to insert multiple rows with hard-coded values into one or more table with a single SQL statement-- it would be more conventional to simply write two separate INSERT statements in that case.

    Justin
  • 2. Re: way INSERT ALL works
    Frank Kulash Guru
    Currently Being Moderated
    Hi, Atanu,
    user11184124 wrote:
    Dear Members :

    If I use the following:

    insert all into emp1 (empno,ename,deptno,sal) values (333,'anthony', 10, 50000)
    into emp2 (empno,ename,deptno,sal) values (333,'anthony', 10, 50000)
    Select empno,ename,deptno,sal from emp;

    14 rows will be inserted into each emp1 and emp2 with values 333,....
    That's right. If you have 14 rows in the emp table, then the query
    Select empno,ename,deptno,sal from emp;           
    produces a result set with 14 rows, and each INTO branch can insert 14 rows.
    but if I use :

    insert all into emp1 (empno,ename,deptno,sal) values (333,'anthony', 10, 50000)
    into emp2 (empno,ename,deptno,sal) values (333,'anthony', 10, 50000)
    Select * from dual;

    just 1 row will be inserted into each emp1 and emp2, as expected.
    Right again; there's only 1 row in the dual table, so the result set of
    Select * from dual;
    contains 1 row, and so each INTO branch can insert 1 row.
    So what's the mechanism of "INSERT ALL", I mean does it execute for each row the subquery returns ?
    Don't try to think about the "mechanism"; that's how procedural languages work. SQL is not a procedural language.
    Every row that the query returns will be available for each INTO branch to use. If you're not using a WHEN clause in a given branch, then every row from the query will be inserted by that branch.
  • 3. Re: way INSERT ALL works
    706771 Newbie
    Currently Being Moderated
    Thanks Frank , Justin.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points