sql往主键自增的表插入数据
1、建立索引
-- Create sequence
create sequence SEQ_NEW_OLD_MGE
minvalue 1400
maxvalue 999999999999999999999999999
start with 1420
increment by 1
cache 20;
注解:创建序列SEQ_NEW_OLD_MGE,最小值1400,最大值999999999999999999(我也不知道是几个9,根据实际随便填),从1420开始,每一次+1,缓冲区20个。
2、使用索引,插入数据
INSERT into new_old_mge (pk,new_id,new_name,old_id,old_name)
select seq_new_old_mge.nextval,p.pm_num,p.pm_name,t.admin_id,t.admin_name from basedata.ts_admin_department t
left join gdbasedata.new_old_mge n on n.old_id = t.admin_id or n.old_name = t.admin_name
left join gdbasedata.port_mge p on p.pm_num = t.admin_id or p.pm_name = t.admin_name
where n.new_id is null and p.pm_num is not null
搞掂。其中,pk是自增的主键,使用序列seq_new_old_mge.nextval来增加。