Insert-SQL

This form of INSERT is actually useful—unlike the Xbase version. It adds one or more records to a specified table, filling in fields at the same time.

Usage

INSERT INTO cFileName [ ( cFieldList ) ] VALUES ( uValueList )
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.

Watch out when you want to insert data from one table into another. The INSERT command changes work areas behind the scenes before it evaluates the new field values, so you need to add the alias to fields from other tables. Similarly, if you have a variable with the same name as a field in the table to which you're adding a record, be sure to use the m. notation.

Example

* Add a record to the Customer table.
INSERT INTO Customer (Customer_Id, Company_name, Contact_name) ;
             VALUES ("MSFT","Microsoft","Bill Gates")

* Create and save a new record.
USE SomeTable
SCATTER MEMVAR BLANK
* Let the user edit the record.
INSERT INTO SomeTable FROM MEMVAR

The second example is typical of code written in FoxPro 2.x to create a new record. The SCATTER would take place in a screen’s Setup snippet (and again after a Save or Cancel), and the INSERT would be in the Valid code for a Save button. With Visual FoxPro’s buffering, there are fewer reasons to edit memory variables, and code like the example has become outdated.

See Also

Append, Append From Array, Gather, Insert, Replace, Set Fields