Множественная вставка SQL oracle

Как вы выполняете множественную вставку с SQL в Oracle 12c, когда у вас есть столбец идентификаторов?

INSERT ALL
INTO Table1 (Column2) Values (1)
INTO Table1 (Column2) Values (2)
SELECT * FROM dual;

где Table1 имеет столбец1 как идентификатор, установит столбец идентичности с тем же значением, который нарушает ограничение первичного ключа.

CREATE TABLE Table1 (
  Table1Id NUMBER GENERATED ALWAYS AS IDENTITY,
  column2 VARCHAR2(255),
  column3 NUMBER,
  PRIMARY KEY (Table1Id)
);

INSERT ALL
  INTO Table1 (column2, column3) VALUES ('a', '1')
  INTO Table1 (column2, column3) VALUES ('b', '2')
SELECT * FROM dual;

--SQL Error: ORA-00001: unique constraint violated

Что я делаю с этим неправильно?

Ответ 1

EDIT Добавлено два тестовых примера и возможное обходное решение.

Хотя оператор Insert и оператор insert all представляют собой практически ту же традиционную инструкцию insert. Но когда дело доходит до последовательностей, они работают по-другому.

Тестовый пример 1: столбцы идентификаторов

SQL> DROP TABLE table1 PURGE;

Table dropped.

SQL>
SQL> CREATE TABLE Table1 (
  2    Table1Id NUMBER GENERATED ALWAYS AS IDENTITY,
  3    column3 NUMBER,
  4    PRIMARY KEY (Table1Id)
  5  );

Table created.

SQL>
SQL> INSERT ALL
  2    INTO Table1 (column3) VALUES ('1')
  3    INTO Table1 (column3) VALUES ('2')
  4  SELECT * FROM dual;
INSERT ALL
*
ERROR at line 1:
ORA-00001: unique constraint (LALIT.SYS_C0010439) violated


SQL>

Посмотрим, что на самом деле происходит под капотом -

SQL> CREATE TABLE Table1 (
  2    Table1Id NUMBER GENERATED ALWAYS AS IDENTITY,
  3    column3 NUMBER,
  4    CONSTRAINT A UNIQUE (Table1Id)
  5  );

Table created.

SQL> INSERT ALL
  2    INTO Table1 (column3) VALUES (1)
  3    INTO Table1 (column3) VALUES (2)
  4  SELECT * FROM dual;
INSERT ALL
*
ERROR at line 1:
ORA-00001: unique constraint (LALIT.A) violated


SQL> SELECT * FROM table1;

no rows selected

SQL> ALTER TABLE table1
  2  DISABLE CONSTRAINT a;

Table altered.

SQL> INSERT ALL
  2    INTO Table1 (column3) VALUES (1)
  3    INTO Table1 (column3) VALUES (2)
  4  SELECT * FROM dual;

2 rows created.

SQL> SELECT * FROM table1;

  TABLE1ID    COLUMN3
---------- ----------
         2          1
         2          2

SQL>

Итак, последовательность прогрессировала до nextval, однако в первый раз, когда мы ввели все, было уникальное нарушение ограничений. Затем мы отключили уникальное ограничение, а последующая Insert All показывает, что последовательность не переходила к следующему, а пыталась вставить дубликаты ключей.

Хотя проблема не возникает с оператором INSERT-INTO-SELECT.

SQL> INSERT INTO table1(column3) SELECT LEVEL FROM dual CONNECT BY LEVEL <=5;

5 rows created.

SQL>
SQL> SELECT * FROM table1;

  TABLE1ID    COLUMN3
---------- ----------
         2          1
         3          2
         4          3
         5          4
         6          5

SQL>

Удивительно, что в соответствии с метаданными последовательность должна автоматически перейти к следующему условию, однако это не происходит с оператором Insert All.

SQL> SELECT COLUMN_NAME,
  2    IDENTITY_COLUMN,
  3    DATA_DEFAULT
  4  FROM user_tab_cols
  5  WHERE table_name   ='TABLE1'
  6  AND IDENTITY_COLUMN='YES';

COLUMN_NAME     IDENTITY_COLUMN DATA_DEFAULT
--------------- --------------- ------------------------------
TABLE1ID        YES             "LALIT"."ISEQ$$_94458".nextval

SQL>

Тестовый пример 2: использование последовательности явно

INSERT ALL будет работать одинаково, независимо от того, используется ли столбец идентификатора или используется явная последовательность.

SQL> DROP SEQUENCE s;

Sequence dropped.

SQL>
SQL> CREATE SEQUENCE s;

Sequence created.

SQL>
SQL> DROP TABLE t PURGE;

Table dropped.

SQL>
SQL> CREATE TABLE t (
  2    ID NUMBER,
  3    text VARCHAR2(50),
  4    CONSTRAINT id_pk PRIMARY KEY (ID)
  5  );

Table created.

SQL>
SQL> INSERT ALL
  2    INTO t VALUES (s.nextval, 'a')
  3    INTO t VALUES (s.nextval, 'b')
  4    INTO t VALUES (s.nextval, 'c')
  5    INTO t VALUES (s.nextval, 'd')
  6  SELECT * FROM dual;
INSERT ALL
*
ERROR at line 1:
ORA-00001: unique constraint (LALIT.ID_PK) violated


SQL>
SQL> SELECT * FROM T;

no rows selected

SQL>
SQL> ALTER TABLE t
  2    DISABLE CONSTRAINT id_pk;

Table altered.

SQL> INSERT ALL
  2    INTO t VALUES (s.nextval, 'a')
  3    INTO t VALUES (s.nextval, 'b')
  4    INTO t VALUES (s.nextval, 'c')
  5    INTO t VALUES (s.nextval, 'd')
  6  SELECT * FROM dual;

4 rows created.

SQL> SELECT * FROM T;

        ID TEXT
---------- ----------------------------------------
         2 a
         2 b
         2 c
         2 d

SQL>

Возможное обходное решение - использование триггера ROW LEVEL

SQL> CREATE OR REPLACE TRIGGER t_trg
  2      BEFORE INSERT ON t
  3      FOR EACH ROW
  4      WHEN (new.id IS NULL)
  5      BEGIN
  6        SELECT s.NEXTVAL
  7        INTO   :new.id
  8        FROM   dual;
  9      END;
 10  /

Trigger created.

SQL> truncate table t;

Table truncated.

SQL> INSERT ALL
  2    INTO t (text) VALUES ('a')
  3    INTO t (text) VALUES ('b')
  4    INTO t (text) VALUES ('c')
  5    INTO t (text) VALUES ('d')
  6  SELECT * FROM dual;

4 rows created.

SQL> SELECT * FROM t;

        ID TEXT
---------- -------------------------
         3 a
         4 b
         5 c
         6 d

SQL>

Ответ 2

Здесь обходной путь с использованием метода UNION ALL вместо метода INSERT ALL. По какой-то причине данные должны быть обернуты в select * from (...) или он будет генерировать ошибку ORA-01400: cannot insert NULL into ("JHELLER"."TABLE1"."TABLE1ID").

insert into table1(column2, column3)
select *
from
(
    select 'a', '1' from dual union all
    select 'b', '2' from dual
);