STATEMENT:  SELECT INTO
SELECT field1[, field2[, ...]] INTO newtable [IN externaldatabase]
FROM source
 
The SELECT INTO statement is used to create a make-table query. It is especially useful for making backup copies of tables and reports, or for archiving records. The following example makes a backup copy of the Customers table:
 
SELECT * INTO [Customers Backup]
FROM Customers;
 
You can also copy tables into another database by using the IN clause:
 
SELECT Suppliers.* INTO Suppliers IN 'Backup.mdb'
FROM Suppliers;
 
You don't need to copy the whole table. If you only want to copy a few fields, you can do so by listing them after the SELECT statement. The following query creates a Fiddlers table by extracting the names of fiddlers from a Musicians table:
 
SELECT Name INTO Fiddlers
FROM Musicians
WHERE Instrument = 'fiddle';
 
The fields which you copy into a new table need not come from just one table. You can copy from multiple tables as demonstrated in the next example which selects fields from the two tables Suppliers and Products to create a new table for Mexican Suppliers:
 
SELECT Suppliers.Name, Product, Products.UnitPrice
INTO [Mexican Suppliers]
FROM Suppliers INNER JOIN Products
ON Suppliers.ProductID = Products.ProductID
WHERE Suppliers.Country = 'Mexico';
 
The SELECT...INTO statement doesn't define a primary key for the new table, so you may want to do that manually.

Copyright 1999-2001 by Infinite Software Solutions, Inc. All rights reserved.
Trademark Information