>The issue is some tables reside on MS SQL, and some are local dbf.
>The old code (all dbfs) used the "APPEND FROM" command.
> That does't work with MS SQL
>
> I currently have something like the following
> SQLEXEC(lnHandle, INSERT into sqlTarget
Values (m1,m2))
> wraped in a loop procssing for each record in the local dbf.
>
>Can I use a single command to bring in or "append" all the recs in a dbf into the MS SQL table.
> Any brief illustration would be great.
> Thanks
Assuming you don't have 'best' in your question (as I'm not an SQL server expert:) check Thread #886406 for 'bulk insert' approach from text files.
Laternatively you could use :
insert into .... select .... from
with OpenRowSet() function if this would be a one time process. ie:lnSeconds = Seconds()
Local lnHandle, lcDatabaseName
lcDatabaseName = "myBulkTest"
lnHandle=Sqlstringconnect('DRIVER=SQL Server;'+;
'SERVER=servername;Trusted_connection=Yes')
If SQLExec(lnHandle, "create database "+lcDatabaseName) < 0
Do errHand
Return
Endif
mySQLExec(lnHandle, "use "+lcDatabaseName)
mySQLExec(lnHandle, ;
" create table myTable"+;
" (line_no int not null,"+;
" order_id int not null,"+;
" product_id int not null,"+;
" unit_price smallmoney not null,"+;
" quantity int not null)" )
mySQLExec(lnHandle, ;
"insert into myTable"+;
" SELECT * "+;
" FROM OPENROWSET('VFPOLEDB', "+;
" '"+_samples+"data\testdata.dbc';'';'',"+;
" 'select * from orditems') " )
? Seconds() - lnSeconds
mySQLExec(lnHandle, "select * from myTable", "myBulk")
SQLDisconnect(lnHandle)
Select myBulk
Browse
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.')Cetin
