一张具体表中数据导入另一张表的
有一张GOOSSORTLIST表中的一段时间内的数据导入到HIS_GOODSSORTLIST表中，然后，清空这段时间内GOODSSORTLIST中的数据。目前GOOSSORTLIST表中的数据在这个时间段内的有6十万条数据，目前使用该方法，数据处理很慢，求优化方法。目前使用方式：
struc_flmx stu_sp
decimal ld_jd
long ll_js,ll_count,ll_i,ll_tmp,ll_commit
string st1,st2,st3,st4,st5,st6,st7,st8,st9,st10,st11,st12,st13,st14,st15,st16,st17,st18,st19,st20,st21,st22,st23
init_progress()
st_title.text='结转药品分类明细数据......'
select count(*) into :ll_js from goodssortlist where occurdate<=:dt2;
if isnull(ll_js) then ll_js=0
do while ll_js>ll_count
declare jz_flmx cursor for select KIND,SERIALNUMBER,CODE,SEQUENCE,BRIEF,OCCURDATE,INAMOUNT,OUTAMOUNT,STOREAMOUNT,INSUM,OUTSUM,STORESUM,STOREHOUSE,BATCHNUMBER,VALIDDATE,WRESOURCEKIND,WSERIALNUMBER,WSEQUENCE,FKAMOUNT,FKSUM,PROVIDE,CBJG,FSJG from goodssortlist where occurdate<=:dt2;
open jz_flmx;
fetch jz_flmx into :st1,:st2,:st3,:st4,:st5,:st6,:st7,:st8,:st9,:st10,:st11,:st12,:st13,:st14,:st15,:st16,:st17,:st18,:st19,:st20,:st21,:st22,:st23;
do while sqlca.sqlcode=0
ll_tmp=0
for ll_i=1 to stu_sp.count
if stu_sp.code[ll_i]=st3 then
ll_tmp=stu_sp.sequence[ll_i]
exit
end if
end for
if ll_tmp=0 then
select max(sequence) into :ll_tmp from goodssortlist where code=:st3;
stu_sp.count++
stu_sp.code[stu_sp.count]=st3
stu_sp.sequence[stu_sp.count]=ll_tmp
stu_sp.isretrieve[stu_sp.count]=false
ll_i=stu_sp.count
end if
if long(st4)<>ll_tmp then
insert into his_goodssortlist(KIND,SERIALNUMBER,CODE,SEQUENCE,BRIEF,OCCURDATE,INAMOUNT,OUTAMOUNT,STOREAMOUNT,INSUM,OUTSUM,STORESUM,STOREHOUSE,BATCHNUMBER,VALIDDATE,WRESOURCEKIND,WSERIALNUMBER,WSEQUENCE,FKAMOUNT,FKSUM,PROVIDE,CBJG,FSJG)
values(:st1,:st2,:st3,:st4,:st5,:st6,:st7,:st8,:st9,:st10,:st11,:st12,:st13,:st14,:st15,:st16,:st17,:st18,:st19,:st20,:st21,:st22,:st23);
if sqlca.sqlcode<>0 then
gs_sqlerr=sqlca.sqlerrtext
rollback;
messagebox('提示信息','药品分类明细数据结转失败,单编号:'+st2+'!~r~n'+gs_sqlerr)
return false
end if
delete from goodssortlist where code=:st3 and sequence=:st4;
if sqlca.sqlcode<>0 then
gs_sqlerr=sqlca.sqlerrtext
rollback;
messagebox('提示信息','删除药品分类明细数据失败,单编号:'+st2+'!~r~n'+gs_sqlerr)
return false
end if
ll_count++
ll_commit++
else
if stu_sp.isretrieve[ll_i]=false then
ll_count++
stu_sp.isretrieve[ll_i]=true
end if
end if
if ll_commit=2500 then
commit;
ll_commit=0
end if
ld_jd=ll_count/ll_js
st_11.text=string(int(ld_jd*100))+'%'
st_10.width=int(st_12.width*ld_jd)
fetch jz_flmx into :st1,:st2,:st3,:st4,:st5,:st6,:st7,:st8,:st9,:st10,:st11,:st12,:st13,:st14,:st15,:st16,:st17,:st18,:st19,:st20,:st21,:st22,:st23;
loop
close jz_flmx;
loop
commit;
return true 很简单的:
1. INSERT ..... SELECT ......
2. DELETE FROM WHERE XXXDATE......
每天几百万的数据都是这么处理的，很快的
页:
[1]