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

 

[Mike Gagnon]

INSERT INTO sdexclude (locationid,date) VALUES ('1',CTOD('1/1/2015'),'2',CTOD('1/2/2015'))
Too many brakets.


Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[JRB-Bldr]

Hi Mike,

Thank you for your quick reply.
From the web searching it appeared as though each 'record' needed to have its own field values enclosed in their own set of parenthesis.

Record 1 values: ('1',CTOD('1/1/2015'))​

Record 2 values: ('2',CTOD('1/2/2015'))​

etc...​

The example you show has one set of over-all parenthesis around 4 field values and my table only has 2 fields.
Note-when I tried your code, I again got an error message:

Too many arguments​

Thanks,
JRB-Bldr

 

[mjcmkrsr]

Dear JRB,

Well, you may use INSERT like follows

Either
INSERT INTO cFileName [ ( cFieldList ) ] VALUES ( uValueList )

or

INSERT INTO cFileName FROM ARRAY aValueArray
| MEMVAR

You can't mix and match the two forms of INSERT. You can either specify a field list and then list individual values, or you can insert all values from an array or from corresponding memory variables. This is an item we'd really like to see improved. Well, actually, you can limit the fields in the second form if you use SET FIELDS, but it leads to the kind of code that gives us nightmares. If you want to go that route, put the SET FIELDS command as close as possible to the INSERT and make sure you've added clear comments. Be sure to turn off SET FIELDS as soon as you're done, too. In the first form, if cFieldList is omitted, uValueList must contain one item for each field in cFileName, in field order. Any fewer results in an error message, "Must specify additional parameters." Any greater results in "Syntax Error." If cFieldList is included, uValueList must match it, item by item.

In the second form, using FROM, you can omit fields, but the exact ability varies with the two sources for the data. With FROM ARRAY, values are placed in fields by taking the array elements in order until you run out of either fields or elements. If the array is two-dimensional, a new record is added for each row. Watch out for this if you use certain commands that could return one- or two-dimensional arrays. One such command is aDBObjects(), where VIEW, CONNECTION and TABLE return a one-dimensional array, while RELATION returns a two-dimensional array.

With FROM MEMVAR, values are placed in any field for which there is a correspondingly named memory variable. This version is great when you've issued SCATTER MEMVAR—you can collect the variable values back up into a new record.

If cFileName isn't already open, INSERT opens it in an available work area and leaves it open. The work area containing cFileName becomes the current work area.

Because it updates the table only once, INSERT INTO is a better choice than APPEND BLANK, GATHER MEMVAR, which updates twice. Index entries and field values are written only once, speeding I/O.

A word to the wise: INSERT isn't generally the best bet for adding new, empty records to be populated by the user. If the table has any field-level rules, INSERT fires the rules right away (regardless of the buffering mode). If you have any rules that prohibit empty fields, you get an error. With table-level rules, you can INSERT empty records as long as you fill the required fields before moving to another record. The alternative is to use APPEND BLANK to add empty records. (You'll still need to be buffered if there are field-level rules.) Save INSERT for cases where you've already got the data in hand.

hth
MK

 

[JRB-Bldr]

Hello MK

Thank you for your reply.

You can either specify a field list and then list individual values

That is precisely what I am attempting to do.
And it works just fine if I am inserting only a single 'record' in each INSERT command.
It is when I attempt to insert multiple 'records' using a single INSERT command (following the examples found on the web) that I run into problems.

Thanks,
JRB-Bldr

 

[mjcmkrsr]

Please read carefulley the comment

you can insert all values from an array or from corresponding memory variables.

Please do also have a look at

http://en.wikipedia.org/wiki/Insert_(SQL)

hth

MK

 

[JRB-Bldr]

MK - since this SQL command is being executed through a Connection from a Non-VFP application, there is no VFP array or memory variables available to utilize.

I have the specific field values and the examples found imply that this could be done by using a single INSERT command

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

Also Olaf shows the following example 3/4 of the way down in

http://www.tek-tips.com/viewthread.cfm?qid=1736999:

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

But he prefaces it by saying: SQL Server has a syntax VFP can't do

So, with that in mind, now I am confused and don't know if VFP SQL syntax will or will not support the Multi-record INSERT's.

Thanks
JRB-Bldr

 

[mjcmkrsr]

Hi,

But you're inserting into a VFP table . Hence please reread my first post.

hth

MK

 

[Olaf Doschke]

You can't insert multiple value tuples in VFP SQL. As simple as that.

Bye, Olaf.

 

[JRB-Bldr]

Olaf - thanks for making it simple.

I'll have to find another way to get the job done.

Thanks,
JRB-Bldr

 

[Mike Lewis]

Jrb,

Where are the values coming from?

If they are coming from another table or cursor, you can do this:

INSERT INTO table (field1, field2, ..., fieldn) ;
SELECT F1, F2, ...., Fn FROM SourceTable

If they are coming from a text file, look to see if the back-end has a bulk insert or import feature.

If they are coming from memory variables, you will need to insert each record individually, within a loop.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

You could go through an array and use APPEND FROM ARRAY. It only pays for larger number of records or tables though, as you need a few lines of code to first create the array in an elegant way. It also only works, if the target table has no memo (,general,blob) fields to fill, as APPEND FROM ARRAY does not fill these.

I use something like this in a case for meta data I want to specify in code in TEXT..ENDTEXT sections.

Create Cursor curTest (id int, name char(10), dDate D NULL)
#Define cnColumnCount 3 && in this case three columns, might use FCOUNT("targettable").

Text To lcData NoShow
1;'john';DATE(2014,12,16)
2;'mike';{^2014-12-16}
3;'jeff';'12/16/2014'
4;'greg';.NULL.
EndText 

Local Array laRows[1]
Local Array laValues[ALines(laRows,lcData,3),cnColumnCount]
Store "" To laValues
For lnRow = 1 To Alen(laRows,1)
   lcRow = laRows[lnRow]
   For lnField = 1 To Alines(laRow,lcRow,3,";")
      laValues[lnRow,lnField] = Evaluate(laRow[lnField]) && evaluate helps to get the field types right and be able to specify them as usual in source code format.
   Endfor
Endfor

Select curTest
Append From Array laValues
Browse

As you see this is much longer than even the three full insert statements. It only pays for the ease of entering records into the TEXT..ENDTEXT section, it doesn't perform better than single inserts. But if you put the array creation into a function it might work out more generically and what remains at the top level code is merely the TEXT..ENDTEXT and calling a function with the text and a target table or cursor name.

Pay attention to using semicolons as field delimiter instead of commas, as commas are useful in expressions. Also notice how I used several ways to define dates. The first two of course are independent of date settings, while the date string in line 3 only is correctly evaluated to a date in case you have british or american settings. Try it removing the single quotation marks from the date in this line. This will evaluate to an empty date. Think about why. Try with SET DATE GERMAN and using our syntax 16.12.2014 and think about why that will give syntax error.

Bye, Olaf.

 

[JRB-Bldr]

Hi Mike,

The values are coming from Webpage objects and their properties in a ASP.Net (VB.Net) application.
In that application certain property values are examined and they are then written into a VFP data table through a Connection and an INSERT command.

It is that VB.Net application that I need to do the coding in - scan the web objects & write the results. But the resultant SQL INSERT command needed to comply with VFP SQL INSERT syntax - so I wanted to check it for correctness first.

Rather than doing each 'record' INSERT individually and sequentially, which is taking a longer time than desired, I wanted to find a more concise, efficient way of doing the multiple INSERTS.

Unless some other means can be identified, I may have to do the work with ASP.Net Mutli-threading.
Yes the INSERT's would still be individual to comply with VFP SQL INSERT syntax, but they could be asynchronous and therefore Faster.

Thanks,
JRB-Bldr

 

[Olaf Doschke]

Mike surely is right in case the values come from anywhere but your source code, for example you have APPEND at hand in that case, whether it's a DBF or Text file.

Bye, Olaf.

 

[Olaf Doschke]

In case you connect to VFP data via OLEDB or ODBC you have means of filling a recordset and committing all data in one go.
In short: In whatever way you would insert a datatabe in C# or VB.NET to SQL Server via OLEDBConnection, you can also do it towards DBFs via the VFPOLEDB provider.

Bye, Olaf.

 

[Olaf Doschke]

...In whatever way, except of course creating T-SQL or SQL statements in general.

Bye, Olaf.

 

[Dan Freeman]

Just for future consideration....

The values are coming from Webpage objects and their properties in a ASP.Net (VB.Net) application.
In that application certain property values are examined and they are then written into a VFP data table through a Connection and an INSERT command.

It is that VB.Net application that I need to do the coding in

Saying this up front probably would have resulted in an entirely different thread.

 

[Olaf Doschke]

This info really would have been good to have from the start.

If you think about what we do in VFP, if we need to address a remote database, you know we don't use remote database sql dialect, we either use bulk insert of a text file or we use the most VFP specific thing, a cursor. Just in it's updatable form. If you only want to insert new data you query SELECT * FROM TABLE WHERE 1=0 into an updatable VFP cursor, then APPEND or INSERT data into it and finally TABLEUPDATE(). It doesn't mater if that cursor is a remote view, cursoradapter cursor or SPT updatable cursor, the same general idea applies.

And in the same way, if VFP is your remote database and any other language should insert data into it, you can make use of a query like SELECT * FROM TABLE WHERE 1=0 via the OLEDB Provider, then have something updaable like a collection or dataset or datatable or tableadapter in case of C#, or an array in PHP or whatever is native there and have a mechanism like VFPs TABLEUPDATE. In case of a tableadapter it has the UPDATE() method.

So you webservice is giving you something like a collection or array, you copy it into a tableadapter you generate from the VFP target table and then update it.

Or you do the thing similar to bulk inserts, your VB code creates a text file and you use VFP to append that into your DBF.

All this does not require SQL statements. SQL Statements are not data loaders. Never. Even, if you can write inserts like in T-SQL creating code, which then executes takes more time than anything else, as you load data, transform it into code and then execute it, which evaluates code into the data types needed and finally insert this. I even often don't go the route of CSV or other text files, even if the bulk insert step is fast. You always have to also create a file and also add that time to the overall time needed.

Bye, Olaf.

 

[JRB-Bldr]

This info really would have been good to have from the start.

Olaf - you are absolutely correct. I should have mentioned it at the beginning.
But my question was not how to do the work in VFP (or VB.Net for that matter).

Instead I was asking (or was intending to ask) about the specifics of the VFP SQL INSERT syntax itself - regardless of where it was coming from.

I was confused about the differences that I was finding in other web references and what I was personally experiencing while testing directly in VFP.
Plus the VFP error message was suggesting that I merely had some simple syntax problem that I was not seeing - instead of it being a totally un-supported syntax approach.

And you cleared up that confusion very well.

Thanks to everyone who offered their suggestions/advice/information on this.

Thanks,
JRB-Bldr

 

[Olaf Doschke]

Well, remove any bracket or add any unneeded bracket into an sql statement in VFP, you won't get any detail info about the root cause of the error, mostly only syntax error, maybe you get pointed to the clause (eg fieldlist, from cluase, join, where and having cluases.

For example:
Select * from browser where "available"
Error message: WHERE clause is invalid.

Select * from browser where available
Error message: Column AVAILABLE is not found

More important. In case you have something like SELECT field1, field2 field3, filed4 you get a result with field1, field3 and field4 and field3 will contain data of field2.

What I'm saying: SQL syntax is very complex and it's hard to parse what you meant, especially if a feature isn't even supported. As you try to add several records you have too many brackets. That's even what Mike Gagnon recognized as the error.

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

Bye, Olaf.