INSERT - SQL Command
Appends a new record to the end of a table that contains the specified field values. The INSERT SQL command has three syntaxes:
Use the first syntax to insert specified values into specified fields in a table.
Use the second syntax to insert the contents of elements from an array, memory variable, or property of an object that match the field names in the table.
Use the third syntax to insert rows from an SQL SELECT command into the specified fields in the table.
INSERT INTO dbf_name [(FieldName1 [, FieldName2, ...])]
VALUES (eExpression1 [, eExpression2, ...])
INSERT INTO dbf_name FROM ARRAY ArrayName | FROM MEMVAR | FROM NAME ObjectName
INSERT INTO dbf_name [(FieldName1 [, FieldName2, ...])]
SELECT SELECTClauses [UNION UnionClause SELECT SELECTClauses ...]
Parameters
INSERT INTO dbf_Name
Specifies the name of the table for appending a new record. dbf_Name can include a path and can be a name expression.[( FieldName1 [, FieldName2 [, ...]])]
Specifies the names of the fields in the new record into which the values are inserted.VALUES (eExpression1 [, eExpression2 [, ...]])
Specifies the field values to be inserted into the new record. If you omit the field names, you must specify the field values in the order defined by the table structure. If eExpression is a field name, it must include the table alias.If SET NULL is ON, INSERT attempts to insert null values into any fields not specified in the VALUES clause.
FROM Source
Specifies to insert data from an array, memory variable, or Visual FoxPro object. The following list describes valid items for Source:ARRAY
ArrayName specifies the array whose data is inserted into the new record.
Starting with the first element, the contents of the elements of the array are inserted into the corresponding fields of the record. The contents of the first array element are inserted into the first field of the new record; the contents of the second array element are inserted into the second field, and so on.
When you include the FROM ARRAY clause, Visual FoxPro disregards any default values for fields.
MEMVAR
MEMVAR specifies that the contents of memory variables are inserted into fields with the same names as the variables. If a variable does not exist with the same name as the field, the field is left empty.
NAME
ObjectName specifies a valid Visual FoxPro object, whose property names match the field names in the table for which you want to insert a new record containing the object's property values. You can specify any valid Visual FoxPro object, which you would typically create using the SCATTER...NAME command. For more information, see SCATTER Command.
If the table has a field that does match an object property, Visual FoxPro disregards the field and leaves it blank as if calling the APPEND BLANK command.
If the type of an object's property does not match the field type in the table, Visual FoxPro generates a data type mismatch message.
Note
Use caution when specifying objects derived from Visual FoxPro classes because many native properties have types that might differ from the fields you are working with and cannot be changed.
If an autoincrementing field exists in the table, you cannot have an object property that matches the autoincrementing field unless you set the SET AUTOINCERROR command to OFF for the data session. Otherwise, Visual FoxPro generates an error. If you use SCATTER...NAME to create the object while SET AUTOINCERROR is set to ON, you can use the REMOVEPROPERTY( ) function to remove any autoincrementing properties to avoid generating an error. For more information, see SET AUTOINCERROR Command and REMOVEPROPERTY( ) Function.
An array, memory variable, or an object is not supported in the Visual FoxPro OLE DB Provider.
SELECT SELECTClauses [UNION UnionClause SELECT SELECTClauses ...]
Retrieves data from specified fields in a table or cursor, using one or more SQL SELECT statements, for insertion into another table or cursor. However, the SELECT statement cannot contain any non-SQL clauses such as the following: INTO, TO, and PREFERENCE clauses; NOFILTER, READWRITE, NOCONSOLE, PLAIN, and NOWAIT options.To combine additional SQL SELECT statements with the first SQL SELECT statement, use the UNION clause. For syntax of the SQL SELECT command, which contains the UNION clause, see SELECT - SQL Command.
Note
When you use SQL INSERT with a SELECT statement, make sure that the data you insert is compatible with the data types in the table into which you are inserting. Visual FoxPro attempts to convert the data types in the cursor created by SQL SELECT into the data types in the corresponding table or cursor column into which the data is inserted. If the inserted data is not compatible, precision might be lost, Date data types are converted to Character data types, and so on.
Remarks
If the table you specify is open, SQL INSERT appends the new record to the table. If the table is open in a work area other than the current work area, it is not selected after the record is appended; the current work area remains selected.
If the table you specify is not open, Visual FoxPro opens it in a new work area, and the new record is appended to the table. The new work area is not selected; the current work area remains selected.
While the SQL INSERT command is executing, the current work area becomes the area into which the new record is inserted. In other words, when the SQL INSERT command is executed, it is in the context of the table being inserted into regardless of the current work area before the command was issued.
After executing the INSERT command, Visual FoxPro positions the record pointer on the new record.
Visual FoxPro updates the _TALLY system variable with the number of rows inserted if you include a SQL SELECT statement. For more information, see _TALLY System Variable.
Examples
Example 1
The following example opens the Employee table and adds one record.
USE employee
INSERT INTO employee (emp_no, fname, lname, officeno) ;
VALUES (3022, "John", "Smith", 2101)
Example 2
The following example uses the USE command to open the Customer table in the TestData.dbc database and the SCATTER command to copy the contents of the current record into variables. COPY STRUCTURE copies the table structure to a new table named Cust2.
INSERT inserts a new record into the Cust2 table from memory variables. SELECT retrieves the data from Cust2, and BROWSE displays the new record.
To clean up, the USE command with no table specified closes the table in the current work area, and DELETE removes Cust2.
CLOSE DATABASES
CLEAR
OPEN DATABASE (HOME(2) + 'Data\TestData')
USE Customer
SCATTER MEMVAR
COPY STRUCTURE TO Cust2
INSERT INTO Cust2 FROM MEMVAR
SELECT CUST2
BROWSE
USE
DELETE FILE cust2.dbf
Example 3
The following example inserts data from the OrdersArchive table from a SELECT statement performed on the Orders table.
INSERT INTO OrdersArchive (order_id, order_date, ship_name) ;
SELECT order_id, order_date, ship_name FROM Orders ;
WHERE order_date >= (DATE()-30)