The Make-Table Query (SELECT…INTO)

With the SELECT…INTO statement, you can simultaneously select fields from one or more tables and create a new table with the result of that selection. The following table selects records from the Products table, in which the Discontinued field is set to True, builds a new table called DiscontinuedProducts, and inserts the selected records into the new table:

SELECT Products.* 
INTO DiscontinuedProducts
FROM Products
WHERE Products.Discontinued = True;

If the table specified with the INTO clause already exists, a trappable run-time error occurs.

Only the names and the data types of the original fields are created in the new table. None of the values for DefaultValue, ValidationRule, Caption properties, and so on, are transferred. The new table is built with no indexes and no referential integrity constraints.

An alternative to this approach is to create the empty table, either through Data Access Objects (DAO) methods or by creating a SELECT…INTO query that returns no records. Then create any indexes you want using SQL ALTER TABLE statements or by using the DAO methods discussed in Chapter 3, “Data Definition and Integrity.” Finally, populate the new table with an append query by using INSERT INTO…SELECT rather than SELECT…INTO. Note that deferring creation of the indexes until after the table is populated speeds up the insert operation.