SQL “Select Into” Statement Usage Tutorial
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.
Select Into statement syntax is the same as with Select syntax. We just need to a
FROM. We can also use
WHERE statement in order to filter rows.
SELECT columns INTO newtable [IN externaldb] FROM oldtable WHERE condition;
columnsspecifies the columns in the oldtable we want to copy. We can also use
*in order specify all columns.
newtableis the new table we want to create or copy.
externaldbis optional. We can use if we will copy from another or external database.
oldtableis table we want to copy from.
conditioncan 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
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
name from table
customer into table named
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
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
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
Turkey from table
customers into new table
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
customer_deal table with
LEFT JOIN on
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
SELECT * INTO allcustomers FROM customers WHERE 5=1;