Hi Bill

It is fairley easy to build a generic program that will build the insert statements from a cursor. Look at fields() and fcount().





>I am fairly new to working with SQL server and am looking for the best way to do inserts and updates. I am working on additions to an existing program and so do not have a VFP DBC to store remote views although I could create a temporary one if needed. The code below is what I am using to send the contents of a temporary VFP table to a SQL server table. It works just fine but I can't believe that it is the best way to do it. With all of the typing and quotes and double quotes and continuation lines, it is a real pain (this example is a table with the least number of fields that I could find).
>
>I have looked all over to find a utility to speed up some of this "grunt" work but the closest I could find was one by Erik Moore (where is he anyway?) that would create a stored procedure on the SQL server. I have a similar question about doing updates. What am I missing?
>
>Thanks,
>Bill
>
>

>lcAddFieldList = "INVNO,ITEM,CUSTNO,LOCTID,DESCRIP,UMFACT,PRICE,QTYORD,QTYSHP,INVDTE,PONUM," + ;
>  "EXTPRICE,STKCODE,TAXABLE,ARSTAT, ARTYPE, GLSALE, TRANLINENO, GLASST, BATCH, ADDUSER, ADDDATE,ADDTIME, " +  ;
>  "CURRID, EXCHRAT, BEXTPRI, BPRICE, TQTYSHP, TQTYORD, TPRICE "
>
>SELECT a_tmpTran
>
>SCAN
>  lcAddValList = "'" + a_tmpTran.INVNO+"'," + ;
>    "'" + a_tmpTran.ITEM+"'," + ;
>    "'" + a_tmpTran.CUSTNO+"'," + ;
>    "'" + a_tmpTran.LOCTID+"'," + ;
>    "'" + a_tmpTran.DESCRIP+"'," + ;
>    STR(a_tmpTran.UMFACT,12,2)+"," + ;
>    STR(a_tmpTran.PRICE,12,2)+"," + ;
>    STR(a_tmpTran.QTYORD,12,2)+"," + ;
>    STR(a_tmpTran.QTYSHP,12,2)+"," + ;
>    "'" + DTOC(a_tmpTran.INVDTE)+"'," + ;
>    "'" + a_tmpTran.PONUM+"'," + ;
>    STR(a_tmpTran.EXTPRICE,12,2)+"," + ;
>    "'" + a_tmpTran.STKCODE+"'," + ;
>    "'" + a_tmpTran.TAXABLE+"'," + ;
>    "'" + a_tmpTran.ARSTAT+"'," + ;
>    "'" + a_tmpTran.ARTYPE+"'," + ;
>    "'" + a_tmpTran.GLSALE+"'," + ;
>    STR(a_tmpTran.TRANLINENO,4,0)+"," + ;
>    "'" + a_tmpTran.GLASST+"'," + ;
>    "'" + a_tmpTran.BATCH+"'," + ;
>    "'" + a_tmpTran.ADDUSER+"'," + ;
>    "'" + DTOC(a_tmpTran.ADDDATE)+"'," + ;
>    "'" + a_tmpTran.ADDTIME+"'," + ;
>    "'" + a_tmpTran.CURRID+"'," + ;
>    STR(a_tmpTran.EXCHRAT,12,2)+"," + ;
>    STR(a_tmpTran.BEXTPRI,12,2)+"," + ;
>    STR(a_tmpTran.BPRICE,12,2)+"," + ;
>    STR(a_tmpTran.TQTYSHP,12,2)+"," + ;
>    STR(a_tmpTran.TQTYORD,12,2)+"," + ;
>    STR(a_tmpTran.TPRICE,12,2)+""
>
>  lcSqlString = "INSERT INTO artran ("+lcAddFieldList+") VALUES ("+ lcAddValList + ")"
>  x = SQLEXEC(lnARHandle,lcSqlString,'artran')
>ENDSCAN
>
>
>