|
<< Click to Display Table of Contents >> INSERT statement |
  
|
|
<< Click to Display Table of Contents >> INSERT statement |
|
Adds one or more new rows of data in a table
INSERT INTO table_reference
[(columns_list)]
VALUES (update_atoms)
Use the INSERT statement to add new rows of data to a single table.
Use a table reference in the INTO clause to specify the table to receive the incoming data.
The columns list is a comma-separated list, enclosed in parentheses, of columns in the table and is optional. The VALUES clause is a comma-separated list of update atoms, enclosed in parentheses. Unless the source of new rows is a SELECT subquery, the VALUES clause is required.
If no columns list is specified, incoming update values (update atoms) are stored in fields as they are defined sequentially in the table structure. Update atoms are applied to columns in the order the update atoms are listed in the VALUES clause. There can be fewer update atoms than there are columns in the table, but there cannot be more.
INSERT INTO "Holdings.dbf"
VALUES (4094095, "INPR", 5000, 10.500, "1/2/1998")
If an explicit columns list is stated, incoming update atoms (in the order they appear in the VALUES clause) are stored in the listed columns (in the order they appear in the columns list). NULL values are stored in any columns that are not in a columns list. When a columns list is explicitly described, there must be exactly the same number of update atoms in the VALUES clause as there are columns in the list.
INSERT INTO "Customer.db"
(CustNo, Company)
VALUES (9842, "Inprise Corporation")
To add rows to one table that are retrieved from another table, omit the VALUES keyword and use a subquery as the source for the new rows.
INSERT INTO "Customer.db"
(CustNo, Company)
SELECT CustNo, Company
FROM "OldCustomer.db"
The INSERT statement only supports SELECT subqueries in the VALUES clause. References to tables other than the one to which rows are added or columns in such tables are only possible in SELECT subqueries.
Note: The INSERT statement can use a single SELECT statement as the source for the new rows, but not multiple statements joined with UNION. To do that, save the UNION join statement to a .SQL file and use that file in the INSERT statement. See Local SQL VIEWs for more information on using .SQL files.
