Share via

INSERT - SQL Command

  • Article

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 the specified fields in the table.
  • Use the second syntax to insert the contents of elements from an array, memory variables, or properties 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 [(fname1 [, fname2, ...])]
   VALUES (eExpression1 [, eExpression2, ...])

-or-

INSERT INTO dbf_name FROM ARRAY ArrayName | FROM MEMVAR | FROM NAME ObjectName

-or-

INSERT INTO dbf_name [(fname1 [, fname2, ...])]
   SELECT [(fname1 [, fname2, ...])] FROM tablename WHERE condition

Parameters

  • INSERT INTO dbf_name
    Specifies the name of the table to which the new record is appended. dbf_name can include a path and can be a name expression.

    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.

    If the table you specify is open, 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.

    While the command is executing, the current work area becomes the area into which the new record is being inserted. In other words, when the INSERT command is executed, it is in the context of the table being inserted into, regardless of what the current work area was before the command was issued.

  • [(fname1 [, fname2 [, ...]])]
    Specifies the names of the fields in the new record into which the values are inserted.

  • VALUES (eExpression1 [, eExpression2 [, ...]])
    Specifies the field values 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 SET NULL is ON, INSERT attempts to insert null values into any fields not specified in the VALUES clause.

    If eExpression is a field name, it must include the table alias.

  • FROM ARRAY ArrayName
    Specifies the array whose data is inserted into the new record.

    The contents of the elements of the array, starting with the first element, 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.

  • FROM 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.

  • FROM 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.

    When specifying an object, if the table has a field that does match an object property, Visual FoxPro disregards the field and leaves it blank as an APPEND BLANK command was called.

    Note   If the type of an object's property does not match the field type in the table, Visual FoxPro generates a data type mismatch error. 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.

    When specifying an object, if an autoincrementing field exists in the table, you cannot have an object property that matches the autoincrementing field unless you use SET AUTOINCERROR 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 REMOVEPROPERTY( ) Function.

  • SELECT [(fname1 [, fname2, ...])] FROM tablename WHERE condition
    Specifies the fields to select from a table or cursor to be inserted into another table or cursor. The SELECT statement cannot contain any nonSQL clauses, for example, INTO or TO clauses such as the following:

    INTODestination, TO FILEFileName [ADDITIVE], TO PRINTER [PROMPT], TO SCREEN, PREFERENCEPreferenceName [NOCONSOLE] [PLAIN] [NOWAIT]

    Visual FoxPro updates the _TALLY system variable with the number or rows inserted.

Remarks

After executing the INSERT command, Visual FoxPro positions the record pointer on the new record.

When you use INSERT with a SELECT statement, you must 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 SELECT cursor into the data types in the corresponding table or cursor column into which it is being inserted. If the inserted data is not compatible, precision might be lost, date data types converted to character data types, and so on.

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 USE to open the Customer table in the testdata database and SCATTER to copy the contents of the current record to 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, USE with no table specified closes the table in the current work area, and DELETE removes cust2.dbf.

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_date FROM Orders ;
   WHERE order_date >= (DATE()-30)

See Also

CREATE QUERY | CREATE TABLE – SQL | MODIFY QUERY | SELECT – SQL | _TALLY System Variable