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 2000 by Infinite Software Solutions, Inc.
Trademark Information
|