SQL: INSERT Statement

Data is added to tables by using the Insert statement. The Insert Into statement can be used to append a record to a table or to append multiple records from one table to another.

The syntax for the Insert statement is :

INSERT INTO table_name (column1, column2,...) 
VALUES (value1, value2,....) 

Example #1

INSERT INTO AntiqueOwners ( OwnerID, OwnerLastName, OwnerFirstName ) 
VALUES (4, 'Peter', 'Novitski')

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. 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 the first field, the second into the second field etc.

This would result in one record being inserted into the AntiqueOwners table. This new record would have a OwnerID of 4, a OwnerLastName of Peter and a OwnerFirstName of Novitski.

Note: 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 separated by commas. Text and date fields also must be enclosed in single quotation marks (‘ ‘).

An AutoNumber field (also referred 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.

Example #2

INSERT INTO AntiqueOwners ( OwnerID, OwnerLastName, OwnerFirstName ) 
SELECT EmployeeIDNo, LastName, FirstName 
FROM EmployeeAddressTable 
WHERE City='Upton'

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.