SELECT...INTO Statement

Description

Creates a make-table query.

Syntax

SELECT field1[, field2[, ...]] INTO newtable [IN externaldatabase]
FROM source

The SELECT...INTO statement has these parts:

Part	Description

field1, field2	The name of the fields to be copied into the new table.
newtable	The name of the table to be created. It must conform to standard naming conventions. If newtable is the same as the name of an existing table, a trappable error occurs.
externaldatabase	The path to an external database. For a description of the path, see the IN clause.
source	The name of the existing table from which records are selected. This can be single or multiple tables or a query.

Remarks You can use make-table queries to archive records, make backup copies of your tables, or make copies to export to another database or to use as a basis for reports that display data for a particular time period. For example, you could produce a Monthly Sales by Region report by running the same make-table query each month.

Notes

You may want to define a primary key for the new table. When you create the table, the fields in the new table inherit the data type and field size of each field in the query's underlying tables, but no other field or table properties are transferred.
To add data to an existing table, use the INSERT INTO statement instead to create an append query.
To find out which records will be selected before you run the make-table query, first examine the results of a SELECT statement that uses the same selection criteria.

See Also

ALL, DISTINCT, DISTINCTROW, TOP predicates, CreateTableDef method ("DAO Language Reference"), FROM clause, IN clause, INSERT INTO statement, SELECT statement, UNION operation, WHERE clause.

Example

This example selects all records in the Employees table and copies them into a new table named Emp Backup.

Sub SelectIntoX()

    Dim dbs As Database
    Dim qdf As QueryDef

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

    ' Select all records in the Employees table 
    ' and copy them into a new table, Emp Backup.
    dbs.Execute "SELECT Employees.* INTO " _
        & "[Emp Backup] FROM Employees;"
        
    ' Delete the table because this is a demonstration.
    dbs.Execute "DROP TABLE [Emp Backup];"

    dbs.Close

End Sub

Example (Microsoft Access)

To try the following examples in Microsoft Access, first create a new query in the Northwind sample database. Close the Show Table dialog box without specifying a table or query. Switch to SQL view, paste an individual example into the SQL window, and run the query.

The following example selects all records in the Employees table and copies them into a new table named Employees Backup:

SELECT * INTO [Employees Backup] FROM Employees;

The next example creates a new table called Sales Representatives that contains only employee records that have the title Sales Representative:

SELECT Employees.FirstName, LastName INTO [Sales Representatives]
FROM Employees
WHERE Title = 'Sales Representative';

The following example makes a copy of the Employees table and places the new table in the assumed database Backup.mdb:

SELECT Employees.* INTO Employees IN Backup.mdb FROM Employees;

The next example assumes a Payroll table with two fields: EmployeeID and Salary. This example creates a new table that contains employee and payroll data for all trainees. The Employees and Payroll tables have a one-to-one relationship. The new table contains all of the data from the Employees table plus the Salary field from the Payroll table.

SELECT Employees.*, Salary INTO Trainees
FROM Employees
INNER JOIN Payroll ON Employees.EmployeeID = Payroll.EmployeeID
WHERE Title = 'Trainee';