Insert All and Column Naming

SQL> create table ins(i int, j int);

Table created.

SQL> insert into ins(i, j) select 1,0 from dual;

1 row created.

SQL> insert into ins(i, j) select 1,1 from dual;

1 row created.

All fine. How about insert all?

SQL> insert all into ins(i, j) select 2,0 from dual;

1 row created.

SQL> insert all into ins(i, j) select 2,1 from dual;

1 row created.

SQL> insert all into ins(i, j) select 2,2 from dual;
insert all into ins(i, j) select 2,2 from dual
                *
ERROR at line 1:
ORA-00918: column ambiguously defined

SQL> select * from ins;

         I          J
---------- ----------
         1          0
         1          1
         2          0
         2          1

Got an error about column naming. The simple insert into does not seem to require column naming. As the 1,1 value pair is inserted fine. Insert all requires different column names in the select list. 2,2 without column naming is not working without naming the columns.

SQL> select 2, 2 from dual;

         2          2
---------- ----------
         2          2

SQL> insert all into ins(i, j) select 2 foo, 2 bar from dual;

1 row created.

The column naming does not have an influence here. The placing does.

SQL> insert all into ins(i, j) select 3 j, 4 i from dual;

1 rows created.

SQL> select * from ins where j=3;

no rows selected

SQL> select * from ins where i=3;

         I          J
---------- ----------
         3          4

How about actual multitable insert?

SQL> insert all into ins(i, j) into ins(i, j) select 5 j, 6 i from dual;

2 rows created.

SQL> select * from ins where j=5;

no rows selected

SQL> select * from ins where i=5;

         I          J
---------- ----------
         5          6
         5          6

Similar behavior. Select list column naming does not have effect which column is populated. I would prefer using values with multitable insert to map the select list columns to the placeholders of inserted column names.

SQL> insert all into ins(i, j) into ins(i, j) values (i, j) select 7 j, 8 i from dual;

SQL> select * from ins where j in (7,8);

         I          J
---------- ----------
         7          8
         8          7