>>>I'm on VFP 7.0. No CursorAdapters in VFP 7 right? Currently my application doesn't use a database container. Do you think I should go through the work of setting up a database and using remote views instead of SQL Pass-thru. Since most of the updates will be less than 500 records, will SQL Pass-thru with scanning the cursor and doing INSERTS not be efficient enough? Will I experience an enormous drop in peformance?
>>
>>I think scanning 500 records and then sending 500 inserts as one batch (using execute ('insert ...') syntax) should be quick enough.
>>
>>You can switch to remote views, but I don't have experience with remote views. With remote views you don't have to write extra code.
>
>So Naomi,
>
>I've written code to do my INSERTS using sql pass-thru:
>
>
>
>lnSuccess = sqlexec(lnConn, "insert into " + lcSchema + lcSourceTable + "(" + lcDestinationFields + ") values(" + lcValuesList + ")")
>
>
>
>I'm building the lcDestinationFields before I start SCANning the table records. This works fine, and is doing ONE record at a time. But takes about 20 minutes to transfer 7500 records. I guess I mis-understood you the last time.
>
>How do I do as you say above and scan about 500 records and send 500 inserts as one batch?
>
>Thanks
Create these insert commands this way:
execute('insert into ... values (...); insert into ... values ...')
It's a bit more tricky to construct (you need to be careful with quotes and double them and also since the insert into command is inside the execute, it's even more trickier, but then this whole command will be executed as one batch).
You don't want to run each individual insert.
Remind me to post a sample tomorrow, I've done exactly this for one of my forms.
If it's not broken, fix it until it is.
My Blog