Multiple insert SQL oracle

Question

How do you do multiple insert with SQL in Oracle 12c when you have an identity column?

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

where Table1 has column1 as an identity, will set the identity column to have the same value which violates the primary key constraint.

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

What am I doing wrong with this?

Answer 1

EDIT Added two test cases, and a possible workaround.

Though Insert statement and insert all statement are practically the same conventional insert statement. But when it comes to sequences, they work differently.

Test case 1 : Identity columns

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>

Let's see what's actually happening under the hood -

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>

So, the sequence progressed to nextval however there was an unique constraint violation the first time we did an Insert All. Next, we disabled the unique constraint, and the subsequent Insert All reveals that the sequence did not progress to nextval, rather it attempted to insert duplicate keys.

Though the issue doesn't occur with a INSERT-INTO-SELECT statement.

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>

Surprisingly, as per the metadata, the sequence is supposed to proceed to nextval automatically, however it doesn't happen with an Insert All statement.

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>

Test Case 2 : Using a sequence explicitly

The INSERT ALL would work the same way whether an identity column is used or an explicit sequence is used.

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>

Possible workaround - Using a ROW LEVEL trigger

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>

Answer 2

Here's a workaround using the UNION ALL method instead of the INSERT ALL method. For some reason the data must be wrapped in a select * from (...) or it will generate the error 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
);