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

Examples

Example 1

The following example opens the Employee table and adds one record.

	Copy Code
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.

	Copy Code
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.

	Copy Code
INSERT INTO OrdersArchive (order_id, order_date, ship_name) ; SELECT order_id, order_date, ship_name FROM Orders ; WHERE order_date >= (DATE()-30)

See Also