>Thanks for your assistance. Its certainly educational.
>
>I can see how creating an updateable view would work with small tables but I eventually want my app to add - say - 10,000 records to a table which may already have several million in it. Unless I'm missing something, that approach wouldn't work. You refer, however, to SQL Server's bulk operations and I've come across other references to that but I can't find an actual working example that would help me formulate a working command for that. If you can point me to a relevant link which has such an example, I would be grateful.
>
>By the bye, I've just tried
>
>SQLPREPARE(gnhandle,"insert into vizkeys (realkey,kcurrent,date_used,kprevious,knext,date_up,crypt_key,mess_hash,ownerkey) values (m.realkey,m.kcurrent,m.date_used,'','',m.date_up,'','',m.ownerkey)",'real')
>
>and got this error message:
>
>"The name 'realkey' is not permitted in this context. Only constants, expressions,or variables allowed here. Column names are not permitted."
>
>which makes no sense to me whatever, as Column names would seem to be an obvious requirement when we're inserting records!!
Harry,
Prefix the m.* in values with ?
Before bulk operations pls note that you could use this to insert 10K records into millions rec table.
You'd start with something :
'select * from mytable where 1=2','mySPTCursor')
* Get only the structure - nodata
*Set other updatenames etc
CursorSetProp('Buffering',5,'mySPTCursor')
Put 10K records - now it's a local cursor.
TableUpdate(2,...) && send 10K recs back to server
Bulk operations need some further reading. Here is a quick rough sample :
use employee
COPY fields emp_id, first_name, last_name to SQLBULK.txt TYPE DELIMITED WITH "" WITH TAB
Local lnHandle, lcDatabaseName
lcDatabaseName = "myBulkTester"
lnHandle=SQLStringConnect('DRIVER=SQL Server;'+;
'SERVER=server;Trusted_connection=Yes')
If SQLExec(lnHandle, "create database "+lcDatabaseName) < 0
Do errHand
Return
Endif
mySQLExec(lnHandle, "use "+lcDatabaseName)
mySQLExec(lnHandle, ;
"create table myTable"+;
" (empid varchar(6), first varchar(20), last varchar(20))")
cSQL = [BULK INSERT ]+lcDataBaseName+[..myTable ] + ;
[FROM ']+sys(5)+curdir()+'SQLBULK.txt'+[' ] + ;
[WITH (DATAFILETYPE = 'char')]
mySQLExec(lnHandle, cSQL)
SQLDisconnect(lnHandle)
Function mySQLExec
Lparameters tnHandle, tcSQL, tcCursorName
tcCursorName = iif(empty(tcCursorName),'',tcCursorName)
If SQLExec(tnHandle,tcSQL,tcCursorName) < 0
Do errHand with tcSQL
Endif
Function errHand
Lparameters tcSQL
lcError=tcSQL+chr(13)
Aerror(arrCheck)
For ix=1 to 7
lcError = lcError+trans( arrCheck [ix])+ chr(13)
Endfor
Messagebox(lcError,0,'Error def.')
Next there is SQL server's OpenRowSet which could open say a free or nonFree VFP table as if it's in SQL server and get data from there using insert into/select. There is linked server option which could add a VFP dbc into SQL server's linked list etc
Variations are a lot:)
Cetin