Thanks Mark for your detailed explaination, i really appreciate it.

Reg the different table name , its all the same, i wanted to paste a sample table but i ended up pasting from my source code.

This is a migration of old legacy data to the new oracle database . That's why i had begin trans inside the loop.But i could use your logic to not to insert if one table fails.

Thanks for the tip on Tableupdate.

"There is a better way to do this whithout inserting into a view then updating that view"
Could you tell me what is that..

One problem i'm facing now is , if my remote view is empty without any data,all my insert go fine, but with data (huge 3gb) my insert fails sporadicly.

Thanks for your suggestions..



>I am a bit confused on your cursor/view names in the code. It looks like you open 3 views -- migrate, test!correspondent and test!profile. However, your first insert is into test!correspondent, but the following TABLEUPDATE specifies ccu_correspondent. Secondly, if you are going to update after each insert, the first parameter in the TABLEUPDATE function should either be .F. or 0. There is not need to tell VFP to update all records in the cursor after every new record is inserted. If the update is succeeding, there is no need to have VFP look for all updated records. If the update fails, then the record will probably always fail to update when trying to update all records. You need to find out why it is failing.
>
>There is a better way to do this whithout inserting into a view then updating that view. However, I will work with what you have here, and based on your feedback, we can look at better ways later. Based on what I am understanding, I have modified your code as follows (please look for my questions prefixed by *** throughout your code):

>
>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 0
>Use migrate
>Select 0
>Use test!correspondent
>Select 0
>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 migrate
>
>&& 2 things here, if you are issuing a TABLEUPDATE after every insert, BEGIN TRANS is meaningless.
>&& second, if you want no records inserted if one fails, then put BEGIN TRANS outside the scanning
>&& loop.
>
>*** Begin Transaction
>
>SCAN
>
>   SQLEXEC(gnConnHandle,"select seqsystemkey.nextval from dual", "crsNextVal")
>   Store Round(crsNextVal.nextval,0) To systemkey
>   use in Select("crsNextVal")  && not really needed
>
>   Insert Into 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(.F.,.T.,'correspondent')
>
>   If lCorrespondent=.F.
>     * MESSAGEBOX('Corespondent update failed')
>     Select migrate
>     Replace exceptions With ' Corespondent update failed ' AddI
>   Endif
>
>   *** if the first update fails, do you really want to do this next insert?
>
>   Select profile
>
>   *** why was this next line of code Insert Into ccu!profile? above it was test!profile.
>   *** once the cursor is opened, do not prefix the cursor name with the DB name.
>
>   Insert Into 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(.F.,.T.,'profile')
>
>   If lProfile=.F.
>     * MESSAGEBOX('Profile table update failed')
>     Select migrate
>     Replace exceptions With 'Profile table update failed' AddI
>   Endif
>
>   if not lProfile or not lCorrespondent
>      exit
>   endif
>
>ENDSCAN
>
>*** these last lines of code are not needed. see comment on BEGIN TRANS above.
>
>*** if lProfile and lCorrespondent
>***    end trans
>*** else
>***    rollback
>*** endif
>