Insert All with Identity Column

View All Public Scripts Login to Run Script

Statement 1

create table a(b number generated always as identity, c number, constraint a_pk primary key (b))

Table created.

Statement 2

insert all into a(c) values (lvl) 
           into a(c) values (lvl*10) 
select level lvl from dual connect by level <= 2

ORA-00001: unique constraint (SQL_ZAEKVOFOCDQFWSOBAFBCJIYGP.A_PK) violated ORA-06512: at "SYS.DBMS_SQL", line 1721

Statement 4

create table a(b number, c number, constraint a_pk primary key (b))

Table created.

Statement 6

create trigger a_pk_trg before insert on a for each row 
begin 
  :new.b := a_s.nextval; 
end;

Trigger created.

Statement 7

insert all into a(c) values (lvl) 
           into a(c) values (lvl*10) 
select level lvl from dual connect by level <= 2

4 row(s) inserted.

Statement 8

select * from a


B C
1 1
3 2
2 10
4 20

4 rows selected.

Statement 12

create table a(b number generated always as identity, c number, constraint a_pk primary key (b))

Table created.

Statement 13

insert all into a(c) values (lvl) 
           into a(c) values (lvl*10) 
select level lvl from dual connect by level <= 2

ORA-00001: unique constraint (SQL_ZAEKVOFOCDQFWSOBAFBCJIYGP.A_PK) violated ORA-06512: at "SYS.DBMS_SQL", line 1721

Statement 15

create table a(b number, c number, constraint a_pk primary key (b))

Table created.

Statement 17

create trigger a_pk_trg before insert on a for each row 
begin 
  :new.b := a_s.nextval; 
end;

Trigger created.

Statement 18

insert all into a(c) values (lvl) 
           into a(c) values (lvl*10) 
select level lvl from dual connect by level <= 2

4 row(s) inserted.

Statement 19

select * from a


B C
1 1
3 2
2 10
4 20

4 rows selected.

Additional Information