Hello all

I have a huge table (900,000 records) i need to insert data (70,000) records to it. I have created a sample dbc and added tables as remote view from the oracle. here's the sample of code i use, but some records doesn't get updated. Is my logic correct ? any suggestions is welcome.

thanks,

close all
Store Sqlstringconnect('dsn=dsTEST;uid=test;pwd=mtestn') To gnConnHandle

If gnConnHandle < 0
= Messagebox('Cannot make connection', 16, 'Oracle Connect Error')
Else
Messagebox('Connection made', 48, 'Oracle Connect Message')
Endif

Open Database "c:\documents and settings\test\desktop\test\test.DBC"

Set Multilocks On
l=1
e=1
Select a
Use migrate
Select b
Use test!correspondent
Select c
Use test!profile
Wait Window "Please wait indexing profile table...." Nowait
Index On system_id Tag psysid
Set Order To psysid
GO bott

=CursorSetProp("Buffering",5,"profile")
=CursorSetProp("Buffering",5,"ccu_correspondent")

Select a

Do While Not Eof()
Begin Transaction

=SQLEXEC(gnConnHandle,"select seqsystemkey.nextval from dual ")
Store Round(sqlresult.nextval,0) To systemkey
Select sqlresult
Use

Insert Into test!correspondent (system_id,last_update,firstname,m_initial,lastname,org,;
mail_1,mail_2,city,zip,prefix_id,state_id,multi_cand,county_id) Values ;
(correspkey,Datetime(),Alltrim(migrate.firstname),migrate.Initial,Alltrim(migrate.lastname),Alltrim(migrate.org),;
ALLTRIM(migrate.mail_1),Alltrim(migrate.mail_2),Alltrim(migrate.city),migrate.zip,prefixid,stateid,Alltrim(multicand),countyid)

lCorrespondent=Tableupdate(.T.,.T.,'ccu_correspondent')

If lCorrespondent=.F.
* MESSAGEBOX('Corespondent update failed')
Select a
Replace exceptions With ' Corespondent update failed ' AddI
Endif

Select profile

Insert Into ccu!profile (system_id,docnumber,docname,typist,author,documenttype,last_edited_by,;
last_locked_by,last_access_id,Application,Form,storagetype,creation_date,creation_time,;
default_rights,Status,abstract,Path,docserver_loc,doc_status,CCU_ACTION,ccu_addressee,CCU_COR_TYPE,;
ccu_cc,ccu_route,ccu_date,ccu_dec_num,ccu_gov_num,ccu_due_date,ccu_file_loc,ccu_corr_date,ccu_correspondent,ccu_multi_corr,fulltext) Values;
(systemkey,profilekey,ALLTRIM(migrate.docname),authortypist,authortypist,docutype,authortypist,;
authortypist,authortypist,2,formval,'K',migrate.rte_date1,Datetime(),;
1,0,Alltrim(migrate.notes),pathid,doclocid,'I',actionid,addresse,cortype,;
ALLTRIM(migrate.Copies),migrate.route_1,migrate.rte_date1,Allt(Str(migrate.ident_num)),migrate.govnr_num,migrate.due_date,Alltrim(migrate.file_loc),Ctod(migrate.date_recv),correspkey,multicand,'Y')

lProfile=Tableupdate(.T.,.T.,'profile')

If lProfile=.F.
* MESSAGEBOX('Profile table update failed')
Select a
Replace exceptions With 'Profile table update failed' AddI
Endif

if lprofile and lcoresspondent
end trans
else
rollback
endif

sele 1
skip
loop
enddo