SQL provides different statements in order to select into existing table, variable, temp table in various database servers like Oracle, MySQL, PostgreSQL etc. In this tutorial we will examine how to use Select Into SQL statement for different cases.

Syntax

Select Into statement syntax is the same as with Select syntax. We just need to a INTO between SELECT and FROM. We can also use WHERE statement in order to filter rows.

SELECT columns INTO newtable [IN externaldb] FROM oldtable WHERE condition;

• columns specifies the columns in the oldtable we want to copy. We can also use * in order specify all columns.
• newtable is the new table we want to create or copy.
• externaldb is optional. We can use if we will copy from another or external database.
• oldtable is table we want to copy from.
• condition can be used if we want to filter or specify the data we want to copy. It is the same fashion with regular WHERE usage.

Copy All Columns Into New Table

We will start with a simple and generic example. We will copy all columns from table named customer into table names allcustomers .

SELECT * INTO allcustomers FROM customers;

Copy Specified Columns Into New Table

We can also specify the columns we want to into new table. In this example we will copy columns named id , name from table customer into table named allcustomers

SELECT id,name INTO allcustomers FROM customers;

Create Backup Copy Of Table

Another useful scenario of Select Into statement is creating backup of a table by copying is fully another table. We will create backup of table named customers with the name of cusotmersbackup

SELECT * INTO customersbackup FROM customers;

Copy Table From Another Database

The default behavior of Select Into statement is copy tables in the current database. If we need to copy table into new database we need to use In statement. In this example we will copy table named customers into new database newdb with the table name newcustomers .

SELECT * INTO newcustomers IN 'newdb' FROM customers;

Copy Only Specified Rows Into New Table

As stated previously we can copy only specified or filtered rows during Select Into . We will use WHERE clause with the condition we want to apply. In this example we will copy users with Country Turkey from table customers into new table allcustomers .

SELECT * INTO allcustomers FROM customers WHERE Country='Turkey';

Copy From More Than One (Multiple) Table Into New Table

We can use JOIN with the SELECT INTO . This will provide us to copy from multiple tables into new table. In this example we will copy from tables customers and deals into customer_deal table with LEFT JOIN on customerid

SELECT customers.name,deals.dealid INTO customer_deal FROM customers 
LEFT JOIN deal ON customers.customerid = deals.customerid;

Create New Empty Table

Another useful usage case is creating new empty table with the given table structure and columns. We will use standard syntax and filter wit 5=1 which is always false and do not copy any data from source table. In this example we will create a new table allcustomer from table customers .

SELECT * INTO allcustomers FROM customers WHERE 5=1;