SELECT INTO Statement

Selects data from existing tables or queries into a new table.

 

Syntax

 

SELECT [ALL | DISTINCT | DISTINCTROW | TOP n [PERCENT]]

column [, column ...]
INTO tableNew FROM table [, table ...]
[WHERE ...] [SPLIT BY ...] [LEAVING ...] [GROUP BY ...]

[HAVING ...] [ORDER BY...]

 

The SELECT INTO statement has these parts:

 

Part

Description

n

The number of records or percent in TOP or TOP PERCENT

column

The name of a column or an asterisk, possibly qualified with the name of a table as in [Table].[Column], or [Table].*, or an expression. Can include an alias. A non-qualified asterisk selects all columns, and must be the only item in the column list.

table

The name of the table containing the data (with a possible alias, and column filter), or another query, or several tables or queries joined together with one of the join operators.

tableNew

The name of the table to be created (must conform to standard naming conventions). If the name of the new table is the same as the name of an existing table, the statement fails.

 

Remarks

 

We can use SELECT INTO queries to archive records, make backup copies of our tables, or make snapshots of complex or frequently-changing data for reports. For example, we could produce a Monthly Sales by Region report by running the same SELECT INTO query each month.

 

When we use SELECT INTO to create a new table, the columns in the new table inherit the data type and size, the formatting, and the transfer rules of each column in the original tables.

 

To add data to an existing table, use the INSERT INTO statement.

 

To find out which records will be selected before we run the SELECT INTO query, we can first examine the results of a SELECT statement that uses the same selection criteria.

 

The SELECT INTO statement is an action query and cannot be used from within the Manifold ODBC driver.

 

Example

 

Select all records in the "Employees" table and copies them into a new table named "Backup Copy":

 

 

SELECT * INTO [Backup Copy] FROM [Employees];