SQLEXEC(): Inserting Multiple Records From an External Table 1

[wsjames]

I have multiple records (from an external query) that I would like insert into a database using the SQLEXEC() function. I have successfully connected to a server and am able to insert single records without a problem. But, have not figured out how to pass-through and ultimately insert multiple (external) records into the database. Please help.

 

[Mike Lewis]

It's very easy. You just combine an INSERT command with a SELECT. For example:

INSERT INTO TargetTable ;
(Field1, Field2, Field2) ;
SELECT Fld1, Fld2, Fld3 FROM SourceTable

Of course, the SELECT can include a WHERE clause, as well as ORDER BY, grouping, joins, etc. The constraint is that the total number of columns returned must match the number in the field list in the INSERT.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[wsjames]

I had attempted to use the insert command but continued to receive a -1 return value from my SQLEXEC() call. Oddly enough, the following works:

nRunCmd = SQLEXEC(nHandle, "insert into tblAvailableEquipment (eqp_name) ;
values ('TESTER 9500')")

However the following will not:

nRunCmd = SQLEXEC(nHandle, "insert into tblAvailableEquipment (eqp_name) ;
select eqp_name from cTmp1")

cTmp1 is an external cursor and I've verified the field names. I've no doubt I'm am over-looking something simple.

 

[Mike Lewis]

cTmp1 is an external cursor

What do you mean by an external cursor? Is it a VFP cursor (in which case it won't be visible to the back end)?

There are two things you could try:

- Run the command directly on the back end, using its command line or whatever (how you do it depends on which back end you are using). That will at least tell you whether the error is caused by VFP.

- After getting -1 back from SQLEXEC(), call AERROR() to determine the actual error code returned from the server.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[markros]

You can not use an external cursor. You can only use a real table (file on the disk). You may save your cursor with COPY TO command to a table, then your insert command is going to work (perhaps you may need to specify the full path to the table, though).

 

[Mike Lewis]

Markros,

You may save your cursor with COPY TO command to a table, then your insert command is going to work

Are you sure about that? I would be surprised if most back end database servers would work with VFP's DBF files. But, of course, you're right that you can't use a VFP cursor.

WSJames: By the way, it would help if you could let us know which database you are using.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[markros]

Mike,

I'm not 100% sure it would work with the free tables, but the odds are high. I'm 100% certain it would not work with the cursor.

 

[wsjames]

Firstly, thank you for each of your responses. I've been attmepting to pass a VFP cursor through the SQLEXEC() command. Essentially, I am attempting to write records from a VFP table to an Access database.

Is there an easier way?

 

[Mike Lewis]

I've been attmepting to pass a VFP cursor through the SQLEXEC() command.

As I said earlier, you can't reference a VFP cursor or table in this way. The command you are sending to Access will be executed entirely by the Access ODBC driver. It cannot know anything about Foxpro tables or cursors.

The easiest way of doing it would be to send each INSERT separately. Something like this:

SELECT SourceTable
SCAN
 lcValue = SourceTable.Field1
  SQLEXEC(nHandle ;
    "INSERT INTO TargetTable (Field1) " + ;
    "VALUES (?lcValue)")
ENDSCAN

This will be much slower than doing it in a single command, but also much simpler.

As an alternative, if you have Access installed, you could perhaps import the source table into Access, then use the original suggestion I showed you, that is, to do the INSERT all in one command. That would work, because the source table would then be part of the Access database.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[wsjames]

Thank you so much for your responses. I greatly appreciate your suggestions, and will put them to use.