0

I am using Oracle 12c. I have a table with an auto increment column, pk_id. For a single row insert I do this:

insert into mytable (colA, colB) values (1,2);

It works. The primary key column pk_id is auto generated.I found how to insert multiple rows in one go here. So I tried this to do batch insert:

insert all
    into mytable (colA, colB) values (2,4) 
    into mytable (colA, colB) values (3,6) 
    into mytable (colA, colB) values (4,8) 
    into mytable (colA, colB) values (5,10) 
select 1 from dual;

But it is showing this error:

An UPDATE or INSERT statement attempted to insert a duplicate key

How can I do batch insert?

EDIT: The table definition:

CREATE TABLE MYTABLE 
(
  PK_ID NUMBER(11) GENERATED ALWAYS AS IDENTITY INCREMENT BY 1 START WITH 1 NOT NULL 
, COLA NUMBER(8) NOT NULL 
, COLB NUMBER(8) NOT NULL 
, CONSTRAINT MYTABLE_PK PRIMARY KEY 
  (
    PK_ID 
  )
  ENABLE 
);
Community
  • 1
  • 1
mshsayem
  • 17,557
  • 11
  • 61
  • 69
  • Oracle has sequences, not auto increment columns. Can you show your table definition? – WW. Oct 19 '15 at 02:40
  • Also: Your syntax does not match the syntax of the accepted answer in the question you linked. For example, you have more semicolons at line ends. – WW. Oct 19 '15 at 02:43
  • @WW Table definition posted. btw, I am using the syntax in the [most voted answer](http://stackoverflow.com/questions/39576/best-way-to-do-multi-row-insert-in-oracle#answer-93724), instead of the accepted one. – mshsayem Oct 19 '15 at 02:48

0 Answers0