Multiple insert SQL oracle

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) violatedSQL>

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) violatedSQL> SELECT * FROM table1;no rows selectedSQL> 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          2SQL>

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          5SQL>

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".nextvalSQL>

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) violatedSQL>SQL> SELECT * FROM T;no rows selectedSQL>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 dSQL>

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 dSQL>

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);