insert multiple row into table using select however table has primery key in oracle SQL

Question

I am facing issue while inserting multiple row in one go into table because column id has primary key and its created based on sequence.

for ex:

create table test (
  iD number primary key,
  name varchar2(10)
);


insert into test values (123, 'xxx');
insert into test values (124, 'yyy');
insert into test values (125, 'xxx');
insert into test values (126, 'xxx');

The following statement creates a constraint violoation error:

insert into test
(
   select (SELECT MAX (id) + 1 FROM  test) as id,
   name from test
  where name='xxx'
 );

This query should insert 3 rows in table test (having name=xxx).

score 3 · Accepted Answer · answered Oct 30 '14 at 13:05

You're saying that your query inserts rows with primary key ID based on a sequence. Yet, in your insert/select there is select (SELECT MAX (id) + 1 FROM test) as id, which clearly is not based on sequence. It may be the case that you are not using the term "sequence" in the usual, Oracle way.

Anyway, there are two options for you ...

Create a sequence, e.g. seq_test_id with the starting value of select max(id) from test and use it (i.e. seq_test_id.nextval) in your query instead of the select max(id)+1 from test.
Fix the actual subselect to nvl((select max(id) from test),0)+rownum instead of (select max(id)+1 from test).

Please note, however, that the option 2 (as well as your original solution) will cause you huge troubles whenever your code runs in multiple concurrent database sessions. So, option 1 is strongly recommended.

score 2 · Answer 2 · answered Oct 30 '14 at 13:06

2

Use

insert into test (
   select (SELECT MAX (id) FROM  test) + rownum as id,
   name from test
  where name='xxx'
 );

as a workaround.

Of course, you should be using sequences for integer-primary keys.

answered Oct 30 '14 at 13:06

René Nyffenegger

39,402
33
158
293

Jakob · Answer 3 · 2014-10-30T13:42:10.000

0

If you want to insert an ID/Primary Key value generated by a sequence you should use the sequence instead of selecting the max(ID)+1.

Usually this is done using a trigger on your table wich is executed for each row. See sample below:

CREATE TABLE "MY_TABLE" 
(   
  "MY_ID" NUMBER(10,0) CONSTRAINT PK_MY_TABLE PRIMARY KEY , 
  "MY_COLUMN" VARCHAR2(100)
);
/ 
CREATE SEQUENCE  "S_MY_TABLE" 
MINVALUE 1 MAXVALUE 999999999999999999999999999
INCREMENT BY 1 START WITH 10 NOCACHE  ORDER  NOCYCLE  NOPARTITION ;
/
 CREATE OR REPLACE TRIGGER "T_MY_TABLE" 
  BEFORE INSERT
  ON
  MY_TABLE
  REFERENCING OLD AS OLDEST NEW AS NEWEST
  FOR EACH ROW
    WHEN (NEWEST.MY_ID IS NULL)
DECLARE
   IDNOW NUMBER;
  BEGIN
    SELECT  S_MY_TABLE.NEXTVAL INTO IDNOW FROM DUAL;
    :NEWEST.MY_ID := IDNOW;
  END;

/
ALTER TRIGGER "T_MY_TABLE" ENABLE;
/
insert into MY_TABLE (MY_COLUMN) values ('DATA1');
insert into MY_TABLE (MY_COLUMN) values ('DATA2');
insert into MY_TABLE (MY_ID, MY_COLUMN) values (S_MY_TABLE.NEXTVAL, 'DATA3');
insert into MY_TABLE (MY_ID, MY_COLUMN) values (S_MY_TABLE.NEXTVAL, 'DATA4');
insert into MY_TABLE (MY_COLUMN) values ('DATA5');
/
select * from MY_TABLE;

edited Oct 30 '14 at 13:42

answered Oct 30 '14 at 13:10

Jakob

778
3
9
25

Jakob, trigger-based sequence generation has all the performance-related dangers of heavy context switching. For each row, it does two unnecessary switches, while in fact no switches are needed. I don't recommend doing this. It's always better to use sequences directly in inserts. Laziness of a developer is not an excuse. – peter.hrasko.sk Oct 30 '14 at 13:14
We used this method when we ported a legacy application from SQL-Server (with IDENTITY columns) to Oracle. In our current environment the sequences are used directly. – Jakob Oct 30 '14 at 13:28
1

That makes sense, thank you for explaining. I'd like to advise on using the `when` clause for the triggers to avoid running their code when the `id` gets filled already in the calling SQL context. Not much of an improvement, but does some relief for the DB when the developers are not lazy. :-) – peter.hrasko.sk Oct 30 '14 at 13:31
Thanx for your advice, I edited the sample code as you suggested – Jakob Oct 30 '14 at 13:42

insert multiple row into table using select however table has primery key in oracle SQL

3 Answers3