oracle insert 查询出来的结果批量插入一个表 【oracle一次插入多条数据(insert all)】_oracle将查询结果insert到表

1 插入语句包含主键

create sequence seq_test_insert 
minvalue 1
maxvalue 999999999999999999999999
start with 1
increment by 1
cache 20;

INSERT INTO table1 (SID, field1, field2 )
 
SELECT seq_test_insert.nextval,  t2.field1, t2.field2  FROM table1 t2

2 插入语句没有主键，添加触发器

--创建表的触发器(用来触发id自增)
--DROP SEQUENCE test_seq;
 
create sequence test_seq start with -1 increment by -1;
 
--创建表的触发器(用来触发id自增)
--drop trigger test_trigger ;
 
create or replace trigger test_trigger 
before insert or update on table
for each row
  when (new.sid is null)
begin
  select test_seq.nextval into :new.sid from dual;
end;

 然后再执行插入语句

这里没有主键

insert into table (field1,field2) values (val1, val2);

转载地址：

oracle一次插入多条数据(insert all)_gnail_oug的博客-CSDN博客_oracle添加多行数据