问题描述
- 需要需要往同一张表插入 多条 主键递增 其他字段数值完全相同的几条数据 （数据就来自这张表）怎么做？
-
需要需要往同一张表插入 多条 主键递增 其他字段数值完全相同的几条数据 （数据就来自这张表）怎么做？
解决方案
declare
aa integer :=1;
begin
while aa<10
loop
insert into test values('test',aa);
aa:=aa+1;
end loop;
end;
解决方案二：
比如你的表的结构是 a(id,name,age) 插入原来表中id为1,2,3的数据，设置主键递增，做法是
insert into a(name,age) selec name,age from a where id in (1,2,3)
解决方案三：
INSERT INTO 表名（列1，列2） SELECT 列1，列2 FROM 表名；
列中不包含主键
解决方案四：
declare
type vararray is table of varchar2(50) ;
var_arr vararray ;
begin
var_arr := vararray(20030629,20030629);
for i in 1..2 loop
loop
insert into X80_account (X80KEY,RCD,RMD,RS,RSMD,AGENCYKEY,PERSONKEY, BUSINESSKEY,SUPPLIERKEY,DBDATE,DBUSERID,DBBATCH,DBSEQ,DBTRANCODE,DBREFYEAR,DBACTCODE,DBAMOUNT,PAYMENTKEY,CRCODE,CRREFYEAR,CRACTCODE,CRAMOUNT,TRANSFEREDTO)
select (select max(X80KEY)+1 from X80_account) as X80KEY , RCD,RMD,RS,RSMD,var_arr(i) as AGENCYKEY,PERSONKEY, BUSINESSKEY,SUPPLIERKEY,DBDATE,DBUSERID,DBBATCH,DBSEQ,DBTRANCODE,DBREFYEAR,DBACTCODE,DBAMOUNT,PAYMENTKEY,CRCODE,CRREFYEAR,CRACTCODE,CRAMOUNT,TRANSFEREDTO from X80_account
where x80key='2530753'
end loop;
end;
解决方案五：
declare
**type vararray is table of varchar2(50) ;
var_arr vararray ;
begin
var_arr := vararray(20030629,20030629);
for i in 1..2 loop **
loop
insert into X80_account (X80KEY,RCD,RMD,RS,RSMD,AGENCYKEY,PERSONKEY, BUSINESSKEY,SUPPLIERKEY,DBDATE,DBUSERID,DBBATCH,DBSEQ,DBTRANCODE,DBREFYEAR,DBACTCODE,DBAMOUNT,PAYMENTKEY,CRCODE,CRREFYEAR,CRACTCODE,CRAMOUNT,TRANSFEREDTO)
select (select max(X80KEY)+1 from X80_account) as X80KEY , RCD,RMD,RS,RSMD,**var_arr(i) as AGENCYKEY**,PERSONKEY, BUSINESSKEY,SUPPLIERKEY,DBDATE,DBUSERID,DBBATCH,DBSEQ,DBTRANCODE,DBREFYEAR,DBACTCODE,DBAMOUNT,PAYMENTKEY,CRCODE,CRREFYEAR,CRACTCODE,CRAMOUNT,TRANSFEREDTO from X80_account
where x80key='2530753'
end loop;
end;