SQL Insert of Multiple records

[JRB-Bldr]

I have perused this forum and other web references and found what should be the answer, but it is not working for some reason.

The general answer that I found went something like the following:

INSERT INTO table (field1, field2, ..., fieldn) VALUES
(valuea1, valuea2, ..., valuean),
(valueb1, valueb2, ..., valuebn),
...
(valuez1, valuez2, ..., valuezn)​

Simple enough, but for some reason, I am having difficulty getting that to work.

I have a very simple VFP data table (sdexclude):

locationid C(3)​

date D​

With that in mind, I would expect the following should work.

INSERT INTO sdexclude (locationid,date) VALUES ('1',CTOD('1/1/2015')), ('2',CTOD('1/2/2015'))​

I need to do these INSERTS from a Non-VFP application via a Connection by using VFP SQL Syntax to insert multiple records into that VFP table.

But before going to the Non-VFP application I wanted to confirm the VFP SQL syntax would work.

In order to test the VFP SQL syntax itself I tested in the VFP Command window and each individual 'record' above will successfully insert on its own just fine.

But when I attempt (in the VFP Command window) to string the 2 records together (like shown above), I get an error message:

Command contains unrecognized phrase/keyword​

I am guessing that the problem is something simple that I am over-looking.

Any assistance/suggestions you might have to offer would be greatly appreciated.

Thanks,
JRB-Bldr

 

[JRB-Bldr]

So you expect some code parser to point out you try to use an unsupported feature?

I was very appreciative of your doing exactly that when above you said: You can't insert multiple value tuples in VFP SQL. As simple as that.

it's hard to parse what you meant, especially if a feature isn't even supported.

My apologies for not being clear.
I did try to be as clear as I could my original post by defining my recipient VFP data table and by saying: by using VFP SQL Syntax to insert multiple records into that VFP table.

Other web references suggested that the syntax I was trying was supposed to work.

And, over the many years of working with VFP, I have more than once encountered commands and how they might be used which I hadn't used before (or used in that precise manner) and therefore were 'new' to me.
I have done many, many VFP SQL INSERT's before, but surprisingly enough this was the first time I have needed to do the single line multi-record INSERT and the VFP error message (not always as precise as desired) led me to believe that I was merely making some stupid syntax mistake - rather than it being totally un-supported. This led me to ask my question here.

Fortunately you were there with the simple answer - it is NOT SUPPORTED.

And it was greatly appreciated.

Thanks,
JRB-Bldr

 

[Olaf Doschke]

>and the VFP error message (not always as precise as desired) led me to believe that I was merely making some stupid syntax mistake

Well, and that's what I tried to explain in more general now, since you seem to think along the lines a syntax error is indicating you are only slightly off from a working syntax and some small amendment will make something work. If I were you I'd simply double, perhaps triple check my syntax and see VFP doesn't support this.

So to prevent, that you ever think along that line the next time, I was just pointing out the mere impossibility of a parser or error handler giving more meaningful hints and just continuing the discussion. Merely for smalltalk, if you like.

I was just discussing the implication you made about the meaning of a syntax error. Indeed INSERT INTO TABLE VALUES (1,2),(3,4) is only slightly off from a correct syntax INSERT INTO TABLE VALUES (1,2,3,4) but their meanings would differ totally even in a database supporting this. Even the VFP parser udges the wrong statement as SQL Insert. That's because of the nature of sql statements having a lot of valid and invalid variations.

And yet another aspect: In VFP the latter statement is syntactically ok, but would also fail on any table with more or less than 4 fields at runtime only. So even the aspect of VFP compiling SQL is only half way helpful. In the end SQL is an unfortunate syntax overall. I'd say Linq is even more so, but is at least the Linq query syntax is a compromise between the SQL syntax and the Linq method syntax.

Bye, Olaf.