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

Question

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

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

但是，如果我想用不同的主键值复制一行，下面的语句会给我 ORA-01747 错误。

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;

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

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;

上面的 INSERT ALL 在句法上是不正确的。您必须提及 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 和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>