Making Tables With SQL SELECT INTO

Making Tables With SQL SELECT INTO

SQL doesn't just manipulate existing data. You can use an SQL SELECT INTO statement to create a new table. Instead of copying data from one table to another as the INSERT INTO statement does, SELECT INTO copies data from a source table into a new table that the statement creates.

To create a new table based on existing data, use this statement in the form

 SELECT * | col1[, col2, ...] INTO newtable FROM source 

SELECT INTO supports the WHERE, GROUP BY, HAVING, and ORDER BY clauses, which you can use to limit and arrange the data in newtable.

To copy a table's structure to a new table, but copy no data, use the form

 SELECT * INTO newtable FROM source WHERE False 

Because no record can equal False, the SQL statement copies no data to the new table. Keep in mind that this statement copies just the table's column structure. It doesn't set a primary key or create any indexes for the new table.

CAUTION

SELECT INTO replaces an existing table of the same name (newtable). Be careful to check for existing tables of the same name before running the SELECT INTO statement and act accordingly. Simply reacting to the confirmation request that warns you that continuing will delete the table isn't enough because Access deletes the existing table even if you cancel the action.