MS-Access / Getting Started

---

INSERT Statement (Append Query)

Inserts one or more new rows into the specified table or query. When you use the VALUES clause, the database inserts only a single row. If you use a SELECT statement, the number of rows inserted equals the number of rows returned by the SELECT statement.

Syntax

INSERT INTO table-name [({column-name},...)]
  [IN <source specification>]
  {VALUES({literal},...) | select-statement}
  [WHERE <search-condition>];

Notes If you do not include a column name list, you must supply values for all columns defined in the table in the order in which they were declared in the table definition. If you include a column name list, you must supply values for all columns in the list, and the values must be compatible with the receiving column attributes. You must include in the list all columns in the underlying table whose Required attribute is Yes and that do not have a default value. If you include an IN clause in both the INSERT and the FROM clause of the SELECT statement, both must refer to the same source database.

If you supply values by using a SELECT statement, the statement's FROM clause cannot have the target table of the insert as its table name or as an underlying table. The target table also cannot be used in any subquery.

You cannot include an attachment field in the list of column names for an INSERT into a table. If the target table contains an attachment field, you must include the column-name list and specify any other fields into which you want to insert data. It is not possible to insert data into an attachment field using SQL.

You cannot include a calculated data type field in the list of column names for an INSERT into a table. If the target table contains a calculated data type field, you must include the column-name list and specify any other fields into which you want to insert data. It is not possible to insert data into a calculated data type field using SQL.

You cannot include a multi-valued field in the list of column names for an INSERT into a table unless the multi-value field is the only field in the column-name list and you include the Value property of the field. You can include a WHERE clause only when the target of the insert is the Value property of a single multi-valued field. In this case, you use the WHERE clause to specify which rows in the parent table should be affected by the INSERT. If you use a select-statement as the source of the inserted values when the target is the hidden recordset represented by the Value property of a multi-value field, the WHERE clause applies to the target table, not the select-statement, unless you can qualify the column names in the predicate to make it clear that the WHERE clause applies to the selectstatement. You cannot include a WHERE clause filtering the select-statement and a second WHERE clause filtering the target table.

Because Access allows you to define column-value constraints (validation rules in a desktop database), table constraints (validation rule in a desktop database), and referential integrity checks, any values that you insert must pass these validations before Access will allow you to run the query.

Examples

To insert a new row in the tblProducts table, enter the following:

INSERT INTO tblProducts (ProductName,
   CategoryDescription, UnitPrice)
VALUES ('Support Renewal', 'Multi-User', 99);

To insert old event records into a history table and avoid duplicates, enter the following (qxmplArchiveContactEventsByDate):

PARAMETERS LastDateToKeep DateTime;
INSERT INTO tblContactEventsHistory
  (ContactID, ContactDateTime, ContactEventType, ContactNotes )
 SELECT tblContactEvents.ContactID, tblContactEvents.ContactDateTime,
   tlkpContactEventTypes.ContactEventTypeDescription,
   tblContactEvents.ContactNotes
  FROM tlkpContactEventTypes
  INNER JOIN (tblContactEvents
   LEFT JOIN tblContactEventsHistory
   ON (tblContactEvents.ContactID = tblContactEventsHistory.ContactID)
    AND (tblContactEvents.ContactDateTime =
     tblContactEventsHistory.ContactDateTime))
  ON tlkpContactEventTypes.ContactEventTypeID =
   tblContactEvents.ContactEventTypeID
 WHERE (tblContactEvents.ContactDateTime<[LastDateToKeep])
  AND (tblContactEventsHistory.ContactID Is Null);

Although Access accepts the ANSI-standard VALUES clause, you will discover in a desktop database that Access 2003 and earlier convert a statement such as

INSERT INTO MyTable (ColumnA, ColumnB)
VALUES (123, "Jane Doe");

to

INSERT INTO MyTable (ColumnA, ColumnB)
SELECT 123 As Expr1, "Jane Doe" as Expr2;

Access 2010 does not convert a VALUES clause.
To add the Sales Prospect value to the ContactType multi-valued field of the contact in the tblContacts table whose last name is Smith, enter the following:

INSERT INTO tblContacts (ContactType.Value)
VALUES ("Sales Prospect")
WHERE tblContacts.LastName = "Jane";