Oracle INSERT ALL with SELECT giving invalid specification error

Question

I want to use the INSERT ALL statement to insert 2 rows into 2 different tables. But if I want to insert values by myself, the below query works fine.

insert all
into REGIONS values (5,'Africa')
into JOBS values ('ZZZZ','Shop Sleeper',1000,1000)
select * from DUAL;

However if I want to duplicate a row with a different primary key value, the below statement gives me ORA-01747 error.

insert all
into REGIONS (select :REGION_ID,REGION_NAME from REGION where REGION_ID = 4)
into JOBS  (select :JOB_ID,JOB_TITLE,MIN_SALARY,MAX_SALARY where JOB_ID = 'ST_CLERK')
select * from DUAL;

I dont know how to rectify the query. Please help.

Answer 1

insert all
into REGIONS (select :REGION_ID,REGION_NAME from REGION where REGION_ID = 4)
into JOBS  (select :JOB_ID,JOB_TITLE,MIN_SALARY,MAX_SALARY where JOB_ID = 'ST_CLERK')
select * from DUAL;

The above INSERT ALL is incorrect syntactically. You must mention the VALUES keyword, and list the required columns you want to select from the table to insert the rows.

The correct syntax is:

INSERT ALL
INTO REGIONS VALUES
  (
    REGION_ID,
    REGION_NAME
  )
INTO JOBS VALUES
  (
    JOB_ID,
    JOB_TITLE,
    MIN_SALARY,
    MAX_SALARY
  )
SELECT REGION_ID,
  REGION_NAME,
  JOB_ID,
  JOB_TITLE,
  MIN_SALARY,
  MAX_SALARY
FROM region, jobs
WHERE region.column=job.column --> Jon with the required key
AND ...; --> Put the required filter conditions                 

• Test Case #1

With same columns in destination table.

SQL> CREATE TABLE tab1(a NUMBER, b varchar2(20));

Table created.

SQL> CREATE TABLE tab2(a NUMBER, b varchar2(20));

Table created.

SQL>
SQL> INSERT ALL
  2  INTO tab1(A, b) VALUES(empno, ename)
  3  INTO tab2(A, b)VALUES(empno, ename)
  4  SELECT empno, ename FROM emp;

28 rows created.

SQL>

So, all rows are inserted into the tables tab1 and tab2 respectively. Let's see:

SQL> SELECT * FROM tab1;

         A B
---------- --------------------
      7369 SMITH
      7499 ALLEN
      7521 WARD
      7566 JONES
      7654 MARTIN
      7698 BLAKE
      7782 CLARK
      7788 SCOTT
      7839 KING
      7844 TURNER
      7876 ADAMS
      7900 JAMES
      7902 FORD
      7934 MILLER

14 rows selected.

SQL> SELECT * FROM tab2;

         A B
---------- --------------------
      7369 SMITH
      7499 ALLEN
      7521 WARD
      7566 JONES
      7654 MARTIN
      7698 BLAKE
      7782 CLARK
      7788 SCOTT
      7839 KING
      7844 TURNER
      7876 ADAMS
      7900 JAMES
      7902 FORD
      7934 MILLER

14 rows selected.

SQL>

• Test Case #2

With different columns in destination table.

SQL> CREATE TABLE tab1(a NUMBER);

Table created.

SQL> CREATE TABLE tab2(b varchar2(20));

Table created.

SQL>
SQL> INSERT ALL
  2  INTO tab1(A) VALUES(empno)
  3  INTO tab2(b)VALUES(ename)
  4  SELECT empno, ename FROM emp;

28 rows created.

Let's see:

SQL> SELECT * FROM tab1;

         A
----------
      7369
      7499
      7521
      7566
      7654
      7698
      7782
      7788
      7839
      7844
      7876
      7900
      7902
      7934

14 rows selected.

SQL> SELECT * FROM tab2;

B
--------------------
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER

14 rows selected.

SQL>

• Test Case #3

With different columns in destination table and different columns in source table

SQL> CREATE TABLE tab1(A VARCHAR2(20));

Table created.

SQL> CREATE TABLE tab2(b NUMBER);

Table created.

SQL>
SQL> INSERT ALL
  2      INTO tab1(A) VALUES(ename)
  3      INTO tab2(b)VALUES(deptno)
  4      SELECT e.ename ename, d.deptno deptno FROM emp e, dept d
  5      where e.deptno = d.deptno;

28 rows created.

SQL>

Let's see:

SQL> SELECT * FROM tab1;

A
--------------------
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER

14 rows selected.

SQL> SELECT * FROM tab2;

         B
----------
        20
        30
        30
        20
        30
        30
        10
        20
        10
        30
        20
        30
        20
        10

14 rows selected.

SQL>

Answer 2

Try this...

INSERT ALL
INTO REGIONS(REGION_ID,REGION_NAME)
SELECT REGION_ID,REGION_NAME from REGION where REGION_ID = 4
INTO JOBS(JOB_ID,JOB_TITLE,MIN_SALARY,MAX_SALARY)
SELECT JOB_ID,JOB_TITLE,MIN_SALARY,MAX_SALARY where JOB_ID = 'ST_CLERK' 
SELECT * FROM DUAL;