INSERT INTO Statement

Description

Adds a record or multiple records to a table. This is referred to as an append query.

Syntax

Multiple-record append query:

INSERT INTO target [IN externaldatabase] [(field1[, field2[, ...]])]
SELECT [source.]field1[, field2[, ...]
FROM tableexpression

Single-record append query:

INSERT INTO target [(field1[, field2[, ...]])]
VALUES (value1[, value2[, ...])

The INSERT INTO statement has these parts:

Part	Description

target	The name of the table or query to append records to.
externaldatabase	The path to an external database. For a description of the path, see the IN clause.
source	The name of the table or query to copy records from.
field1, field2	Names of the fields to append data to, if following a target argument, or the names of fields to obtain data from, if following a source argument.
tableexpression	The name of the table or tables from which records are inserted. This argument can be a single table name or a compound resulting from an INNER JOIN, LEFT JOIN, or RIGHT JOIN operation or a saved query.
value1, value2	The values to insert into the specific fields of the new record. Each value is inserted into the field that corresponds to the value's position in the list: value1 is inserted into field1 of the new record, value2 into field2, and so on. You must separate values with a comma, and enclose text fields in quotation marks (' ').

Remarks You can use the INSERT INTO statement to add a single record to a table using the single-record append query syntax as shown above. In this case, your code specifies the name and value for each field of the record. You must specify each of the fields of the record that a value is to be assigned to and a value for that field. When you don't specify each field, the default value or Null is inserted for missing columns. Records are added to the end of the table.

You can also use INSERT INTO to append a set of records from another table or query by using the SELECT...FROM clause as shown above in the multiple-record append query syntax. In this case, the SELECT clause specifies the fields to append to the specified target table.

The source or target table may specify a table or a query. If a query is specified, the Microsoft Jet database engine appends records to any and all tables specified by the query.

INSERT INTO is optional but when included, precedes the SELECT statement.

If your destination table contains a primary key, make sure you append unique, non-Null values to the primary key field or fields; if you don't, the Microsoft Jet database engine won't append the records.

If you append records to a table with an AutoNumber field and you want to renumber the appended records, don't include the AutoNumber field in your query. Do include the AutoNumber field in the query if you want to retain the original values from the field.

Use the IN clause to append records to a table in another database.

To create a new table, use the SELECT...INTO statement instead to create a make-table query.

To find out which records will be appended before you run the append query, first execute and view the results of a select query that uses the same selection criteria.

An append query copies records from one or more tables to another. The tables that contain the records you append aren't affected by the append query.

Instead of appending existing records from another table, you can specify the value for each field in a single new record using the VALUES clause. If you omit the field list, the VALUES clause must include a value for every field in the table; otherwise, the INSERT operation will fail. Use an additional INSERT INTO statement with a VALUES clause for each additional record you want to create.

See Also AddNew method ("DAO Language Reference"), FROM clause, IN clause, INNER JOIN operation, LEFT JOIN, RIGHT JOIN operations, SELECT statement, SELECT...INTO statement, WHERE clause.

Specifics (Microsoft Access)

If you create an INSERT INTO...VALUES query in SQL view, save and close the query, and then reopen it, you'll see that Microsoft Access has converted the VALUES clause to a SELECT clause. This doesn't alter the results of the query.

The use of the INSERT INTO statement is equivalent to setting the DestinationTable property in the query's property sheet of an append query in query Design view.

Example

This example selects all records in a hypothetical New Customers table and adds them to the Customers table. When individual columns are not designated, the SELECT table column names must match exactly those in the INSERT INTO table.

Sub InsertIntoX1()

    Dim dbs As Database

    ' Modify this line to include the path to Northwind
    ' on your computer.
    Set dbs = OpenDatabase("Northwind.mdb")

    ' Select all records in the New Customers table 
    ' and add them to the Customers table.
    dbs.Execute " INSERT INTO Customers " _
        & "SELECT * " _
        & "FROM [New Customers];"
        
    dbs.Close

End Sub

This example creates a new record in the Employees table.

Sub InsertIntoX2()

    Dim dbs As Database

    ' Modify this line to include the path to Northwind
    ' on your computer.
    Set dbs = OpenDatabase("Northwind.mdb")

    ' Create a new record in the Employees table. The 
    ' first name is Harry, the last name is Washington, 
    ' and the job title is Trainee.
    dbs.Execute " INSERT INTO Employees " _
        & "(FirstName,LastName, Title) VALUES " _
        & "('Harry', 'Washington', 'Trainee');"
        
    dbs.Close

End Sub

Example (Microsoft Access)

Warning These examples make changes to the Northwind sample database. Before beginning, you may wish to make a backup copy of the sample database.

To try the following example, create a copy of the Customers table and name it NewCustomers. This example selects all records in the New Customers table and adds them to the Customers table. When individual columns aren't designated, the SELECT table column names must match exactly those in the INSERT INTO table.

INSERT INTO Customers SELECT * FROM NewCustomers;

The next example creates a new record in the Employees table:

INSERT INTO Employees (FirstName,LastName, Title)
VALUES ('Harry', 'Washington', 'Trainee');

The following example selects all trainees from an assumed Trainees table who were hired more than 30 days ago and adds their records to the Employees table.

INSERT INTO Employees SELECT Trainees.* FROM Trainees
WHERE HireDate < Now() - 30;

You can use the INSERT INTO clause to back up information in your database before it is changed. For example, you can back up information in an Employees table immediately before a user makes any changes to the data in that table.

To try the following example, in the Database window copy the Employees table to a new table called EmployeeHistory by clicking the table, clicking the Copy button on the toolbar, then clicking the Paste button. Under Paste Options in the Paste Table As dialog box, click Structure Only. This will copy the structure of the table only, without any data. Next paste the following SQL statement into a new query and save the query as BackUpQuery:

INSERT INTO EmployeesHistory (FirstName, LastName, Title)
VALUES (Forms!Employees!FirstName, Forms!Employees!Lastname,
Forms!Employees!Title);

Open the Employees form in form Design view and set the form's BeforeUpdate property to [Event Procedure]. In the BeforeUpdate event procedure, enter the following code:

DoCmd.OpenQuery "BackUpQuery"

Now the existing data in the Employees table will be copied to the backup table each time a user changes it.