Appending With SQL INSERT INTO

Appending With SQL INSERT INTO

Adding new records is a common task, and you can use SQL's INSERT INTO statement to copy data from one table to another or insert a new record into an existing table. When copying records from one table to another, use the form

 INSERT INTO target SELECT source 

where target is the table you're copying the data to and source is the original data you're copying. This statement supports both the WHERE and ORDER BY clauses.

INSERT INTO won't create a new table, so target must exist prior to executing the statement. In addition, the columns you're inserting must exist in target SQL won't create new columns either.

CAUTION

Using the asterisk character (*) in the INSERT INTO clause can return an error if both tables are using AutoNumber fields to produce the primary key value. That's because there's a likelihood that primary key values are duplicated in both tables.

The second form of this statement appends data to an existing table, one record at a time:

 INSERT INTO target [col1[, col2, ...]] VALUES (val1[, val2, ...]) 

There are a few rules you must comply with to use this form successfully:

• Column references are optional. When omitted, you must include a value for each column in the target table.

• The arguments in the VALUES clause must occur in the same order as their corresponding columns in the target table. This order doesn't have to match the order in which the columns occur in the table's design.

The simple statement

 INSERT INTO Employees (LastName, FirstName) VALUES ('Harkins', 'Susan') 

adds a new record for Susan Harkins to the Employees table. Notice that the EmployeeID field isn't referenced. That's because that field is an AutoNumber field, so Access generates its value automatically. If you attempt to apply a value to an AutoNumber field, Access returns an error.