STATEMENT:  INSERT INTO
Single-record:
 
INSERT INTO target [(field1[, field2[, ...]])] VALUES (value1[, value2 [, ...])
 
Multiple-record:
 
INSERT INTO target [IN externaldatabase] [(field1[, field2[, ...]])]
SELECT [source.] field1[, field2[, ...] FROM tableexpression
 
To add a specific record to a table, the INSERT INTO statement is followed by the name of the table and the names of the various fields. If you don't specify each field, a default value or NULL will be inserted for missing columns. Each value in the VALUES clause is inserted into the field that corresponds to the value's position in the list: the first value is inserted into field 1, the second into field 2 etc.
 
INSERT INTO Musicians ( Name, Instrument )
VALUES ('Bobby Lee', 'fiddle');
 
Note that if you omit the field list, you must include a value for every field in the table, otherwise the operation will fail. The values must also be seperated by commas, and text fields enclosed in single quotation marks (' ').
 
When using the multiple-record syntax the INSERT INTO statement precedes a SELECT statement and adds a single or multiple records to a table. This is referred to as an append query, as it copies records from one or more tables to another. The tables that contain the records being appended are not affected by the operation:
 
INSERT INTO Duos ( Member1 )
SELECT Name FROM Musicians;
 
An AutoNumber field (also refered to as a Counter field) is a data type that automatically creates and stores a number for each item added to a table. If you append records to a table with an AutoNumber field and you do not want the AutoNumbered field to be renumbered, you must include the AutoNumber field number in the query. If you do not include the AutoNumber field, the appended items will be assigned a new AutoNumber field value.
 
If the destination table contains a PRIMARY KEY, you can only append unique non-NULL values to the PRIMARY KEY field.
 
The following example would append to the Dous table, only those records that had a unique primary key:
 
INSERT INTO Duos ( Member1 )
SELECT Name FROM Musicians
WHERE MusicianID > 3;
 
You can also append records to a table in another database using the IN clause:
 
INSERT INTO Residents (Name, Occupation) IN Residents.mdb
SELECT Name, Occupation FROM Immigration
WHERE Residency = 'granted';
 
As with a straight-forward SELECT statement, the FROM clause may include more than one table linked by a JOIN operation. This is illustrated in the following example which appends to the 'Insurance' table the names of all those employees involved in the 'Hardwork' project:
 
INSERT INTO Insurance (Name)
SELECT Employee.Username FROM Employee INNER JOIN Project
ON Employee.EmployeeID = Project.EmployeeID
WHERE Project.ProjectName = 'Hardwork';

Copyright 1999-2001 by Infinite Software Solutions, Inc. All rights reserved.
Trademark Information