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;