How to perform multiple inserts in SQL server from VFP

[EzLogic]

How do YOU do your inserts for multiple records in SQL from vfp9?

Example,

I have invoice screen, which has 2 or 3 cursors,
1 cursor hold customer info
1 cursor hold invoice details
1 cursor hold some other table detail

so, now, i do this:

select curInvoice
scan
   && get the 
   lcItem = item
   lcItmDesc = ItmDesc
   lnQty = Qty
   etc..
   
   text to lcSQL noshow
     insert into ARTran
     (item,
      itmdesc,
      qty, 
      etc..
     )
     values
    ( 
     ?lcItem,
     ?lcItmDesc,
     ?lnqty,
     ?lcetc...
    (
   goSQL.ExecuteNonQuery(lcSQL)
   if SQLError() 
      exit
   endif
endscan

I know this is doing a single call for insert for every record.. most invocies have 5-10 lines Max, but, i might be needing this for an invoice that has 200-1000 line items (serialized items)...


Ez Logic
Michigan

 

[EzLogic]

^^ I am missing endtext above for my: TEXT TO lcSQL NOSHOW

that was a typo


Ez Logic
Michigan

 

[Mike Lewis]

I'm not sure I follow what you are trying to achieve, but it looks like you want something like the following:

[tt]INSERT INTO <table name> <field list> SELECT <field list> FROM <table name> etc. etc.[/tt]

Does that help at all?

If not, I'm sure someone else will give you a bette answer.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[EzLogic]

Hi Mike, sorry i wasnt very clear.

Currently, i am successfully inserting in SQL server, and all is great.

However, i am doing a scan and for each scan, i insert the values.

I am wondering if there is a way to do multiple inserts in one command.

example:

lcSQLStr = ''
scan 
  lcField1 = curInvoice.Field1
  lcField2 = curInvoice.Field2
  lcField3 = curInvoice.Field3
  etc..
text to lcSQL noshow
   insert into mySQLServerTable
       (field1,field2,field3) 
       values
       (?lcfield1,?lcField2,?lcField3)
endtext
 lcSQLStr = lcSQLStr + chr(13) + lcSQL
endscan 

gosql.executeNonQuery(lcSQLStr)

However, the values of lcField1, lcField2 they change per record from my cursor.


Ez Logic
Michigan

 

[bborissov]

Use CursorAdapters

Borislav Borissov
VFP9 SP2, SQL Server

 

[EzLogic]

Borislav,

I have never used cursoradapters.

can you give me a sample example and how it applies, if you have a moment please?

Ez Logic
Michigan

 

[Olaf Doschke]

The central function to insert/update and delete records in a remote database is TABLEUPDATE(). Whether you use a cursoradapter or sql passthrough or remote views is less important, bu I'm with Borislav, cursoradapters are the best of the family of updatable cursors to anything.

Google the cursoradapter builder and you should find a tutorial and someones (Bernard Bout's?) advanced CA builders. But even the native builder of VFP is very usable to make all the essential settings step by step and interactively instead of reading through all CA properties and method descriptions.

Aside of that for small amounts of sample data nothing is easier than a INSERT inserting multiple records in one T-SQL Insert statement. SQL Server has a syntax VFP can't do:

INSERT INTO table (someid, firstname)
  values (1,'john')
  ,      (2,'mike')
  ,      (3,'jeff')
  ,      (4,'greg')

So you can specify multiple value tuples in one insert.

Otherwise, if staying with a single SQL-Insert make use of SQLPrepare().

Bye, Olaf.

 

[cthode]

Try something like the following (sorry - don't know how to Post as code!)
[tt]lcSQLStr = ''
lnResult = 0
scan while lnResult <> -1
lcField1 = curInvoice.Field1
lcField2 = curInvoice.Field2
lcField3 = curInvoice.Field3
etc..
text to lcSQL noshow
insert into mySQLServerTable
(field1,field2,field3)
values
(?lcfield1,?lcField2,?lcField3)
endtext
lnResult = gosql.executeNonQuery(lcSQL)
* lcSQLStr = lcSQLStr + chr(13) + lcSQL
endscan

if lnResult = 1
SqlCommit()
else
SqlRollback()
endif
*gosql.executeNonQuery(lcSQLStr)[/tt]
You of course need to add ways to check the result from the executeNonQuery() call and the commit/rollback, and should also use SqlPrepare() for better speed, as pointed out by others.