Into Statement  «Prev 

SQL INTO Statement

There is an interesting use of SELECT...INTO that may come in handy, depending on the SQL engine you are using.
Look into the use of temporary tables, they can be very handy as you work within a given table. Temporary tables automatically go away when you are finished working with them. Typically, the name of the table will be a special name that the engine will recognize as temporary. For example, with the Microsoft products, if you prefix the name with a pound sign, the engine will automatically know that the table should only be created temporarily.


The SELECT INTO statement selects data from one table and inserts it into a new table.

We can copy all columns into the new table:
 INTO newtable [IN externaldatabase]
 FROM table_n;

Or we can copy only the columns we want into the new table:
SELECT column_name(s)
 INTO newtable [IN externaldatabase]
 FROM table_n;
The new table will be created with the column-names and types as defined in the SELECT statement. You can apply new names using the AS clause.

SQL Queries
SQL:2003 introduced CREATE TABLE AS, but Microsoft Access and Microsoft SQL Server use

to create a new table from an existing one:
SELECT columns
INTO new_table
FROM existing_table
[WHERE search_condition];

The SQL standard's version of SELECT INTO is not the same thing. It selects a value into a scalar variable in a host program rather than creating a new table. The Oracle, DB2, and MySQL implementations of SELECT INTO work in the standard way. For portability, you should not use CREATE TABLE AS or SELECT INTO. Instead, create a new, empty table with CREATE TABLE and then populate it with INSERT SELECT.