Oracle INSERT ALL with SELECT 给出无效规范错误

我想使用 INSERT ALL 语句将 2 行插入 2 个不同的表中。 但如果我想自己插入值，下面的查询就可以正常工作。

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

但是，如果我想复制具有不同主键值的行，下面的语句给了我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;

我不知道如何纠正查询。请帮忙。

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;

以上全部插入语法上不正确。你必须提到VALUES关键字，以及列出所需的列您想要从表中选择要插入的行。

正确的语法是：

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                 

• 测试用例 #1

目标表中具有相同的列。

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>

因此，所有行都插入到表中tab1 and tab2分别。让我们来看看：

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>

• 测试用例 #2

目标表中具有不同的列。

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.

让我们来看看：

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>

• 测试用例 #3

目标表中的列不同，源表中的列不同

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>

让我们来看看：

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>