The Append Query (INSERT INTO)

You can append new records to an existing table by using an INSERT INTO query. In the simplest case, literal values are assigned to a single record that’s inserted into the table:

INSERT INTO PerformanceGrade (PerformanceGradeKey, LowRange, HighRange)
VALUES (5, 500000.01, 600000);

INSERT INTO…VALUES

The INSERT INTO query lists the table to which the record will be added, the fields that will be populated with new values, and a corresponding VALUES clause with the literal values that will be assigned to those fields. The number and data types of the fields in the field list and the value list must match.

It’s not necessary to specify every field in the table. Any fields not specified are filled with the value specified by the DefaultValue property for that particular field. However, all required fields must be assigned, including those fields participating in indexes or acting as part of the primary key.

However, an INSERT INTO query can’t violate any referential integrity constraints or ValidationRule property clauses established at either the field level or the table level. If the Required property is set to True for a particular field, for example, and you don’t assign a value for this field, a trappable run-time error is generated when you attempt to run the INSERT INTO query.

Similarly, if a referential integrity constraint has been established that requires a matching value in a foreign key field and that value doesn’t exist in the related table, the INSERT INTO query will fail.

INSERT INTO…SELECT

An alternative form of this statement (and one likely to be created if you generate this query in Microsoft Access) is as follows:

INSERT INTO PerformanceGrade (PerformanceGradeKey, LowRange, HighRange)
SELECT 5 AS Expr1, 500000.01 AS Expr2, 600000 AS Expr3;

Rather than using the VALUES clause to assign field values, this form of the INSERT INTO statement takes its values from another SELECT statement. In this particular case, because the inner SELECT statement doesn’t contain a FROM clause, a single record is inserted into the new table, using the calculated fields Expr1, Expr2, and Expr3 as the source items.

When the source SELECT statement does have a FROM clause, all records retrieved by the SELECT statement are inserted into the first table:

INSERT INTO Employees 
	(EmployeeID, 
	LastName, 
	FirstName, 
	Title, 
	TitleOfCourtesy, 
	BirthDate, 
	HireDate, 
	Address, 
	City, 
	Region, 
	PostalCode, 
	Country, 
	HomePhone, 
	Extension, 
	Photo, 
	Notes, 
	ReportsTo)
SELECT 
	TemporaryEmployees.EmployeeID, 
	TemporaryEmployees.LastName, 
	TemporaryEmployees.FirstName, 
	TemporaryEmployees.Title, 
	TemporaryEmployees.TitleOfCourtesy, 
	TemporaryEmployees.BirthDate, 
	TemporaryEmployees.HireDate, 
	TemporaryEmployees.Address, 
	TemporaryEmployees.City, 
	TemporaryEmployees.Region, 
	TemporaryEmployees.PostalCode, 
	TemporaryEmployees.Country, 
	TemporaryEmployees.HomePhone, 
	TemporaryEmployees.Extension, 
	TemporaryEmployees.Photo, 
	TemporaryEmployees.Notes, 
	TemporaryEmployees.ReportsTo
FROM TemporaryEmployees
WHERE TemporaryEmployees.Title = 'New Hire';

Instead of itemizing the fields in the source and target tables, you can use the shorthand form of this statement:

INSERT INTO Employees
SELECT TemporaryEmployees.*
FROM TemporaryEmployees
WHERE TemporaryEmployees.Title = 'New Hire';

This format requires that the field names in the source and the target tables match.