INSERT – SQL Command

  • Article

Appends a record to the end of a table that contains the specified field values.

INSERT INTO dbf_name [(fname1 [, fname2, ...])]
   VALUES (eExpression1 [, eExpression2, ...])

-or-

INSERT INTO dbf_name FROM ARRAY ArrayName | FROM MEMVAR

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 isn't open, it is opened in a new work area and the new record is appended to the table. The new work area isn't 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 isn't selected after the record is appended; the current work area remains selected.

  • [(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 – SQL attempts to insert null values into any fields not specified in the VALUES clause.

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

    Any default values for fields are ignored when you include the FROM ARRAY clause.

  • FROM MEMVAR
    Specifies that the contents of variables are inserted into fields with the same names as the variables. If a variable doesn't exist with the same name as the field, the field is left empty.

Remarks

The new record contains the data listed in the VALUES clause or contained in the specified array or variables. The record pointer is positioned on the new record.

Example

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)

The following example opens the customer table in the testdata database. The contents of the current record are scattered to variables, and the table's structure is copied to a new table named cust2. INSERT - SQL is used to insert a new record in the cust2 table, and BROWSE is issued to display the new record.

CLOSE DATABASES
CLEAR

OPEN DATABASE (HOME(2) + 'Data\testdata')
USE Customer     && Open customer table
* Scatter current record to memory variables
SCATTER MEMVAR

* Copy structure of current table to example table
COPY STRUCTURE TO cust2

* Insert record from memory variable
INSERT INTO cust2 FROM MEMVAR

SELECT CUST2
BROWSE

* Close and delete example table
USE
DELETE FILE cust2.dbf

See Also

CREATE QUERY | CREATE TABLE – SQL | MODIFY QUERY | SELECT – SQL