3 Replies Latest reply: May 3, 2012 6:08 AM by 706771 RSS

    way INSERT ALL works

    706771
      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
          JustinCave
          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
            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
              Thanks Frank , Justin.