SQL SELECT INTO Statement

In this tutorial, we'll learn to copy data from one table to another in SQL with the help of examples.

In SQL, we can copy data from one database table to a new table using the SELECT INTO command. For example,

SELECT *
INTO CustomersCopy
FROM Customers;

Here, the SQL command copies all data from the Customers table to the new CustomersCopy table.

Note: The SELECT INTO statement creates a new table. If the database already has a table with the same name, SELECT INTO gives an error.

If we want to copy data to an existing table (rather than creating a new table), we should use the INSERT INTO SELECT statement.


Copy Selected Columns Only

We can also copy only selected columns from the old table to a new table. For example,

SELECT customer_id, country
INTO CustomersCountry
FROM Customers;

Here, the SQL command only copies customer_id and country columns to the CustomersCopy table.


Copy Records Matching a Condition

We can use the WHERE clause with SELECT INTO to copy those rows that match the specified condition. For example,

SELECT customer_id, age
INTO USACustomersAge
FROM Customers
WHERE country = 'USA';

Here, the SQL command

  1. creates the USACustomersAge table with customer_id and age column
  2. copies the rows to the new table if the value of the country column is USA.

Copy to Another Database

By default, SELECT INTO creates a new table in the current database. If we want to copy data to a table in a different database, we can do that by using the IN clause. For example,

SELECT *
INTO CustomersCopy
IN another_db.mdb
FROM Customers;

Here, the SQL command copies the Customers table to the CustomersCopy table in the another_db.mdb database.

Note: The user must have WRITE privilege to copy data to a table in a different database.


Copy From Two Tables to One

We can also copy records from two different tables to a new table using the JOIN clause with SELECT INTO. For example,

SELECT Customers.customer_id, Customers.first_name, Orders.amount
INTO CustomerOrders
FROM Customers
JOIN Orders
ON Customers.customer_id = Orders.customer_id;

Here, the SQL command copies customer_id and first_name from the Customers table and the amount from the Orders table to a new table CustomerOrders. To learn more, visit SQL JOIN.


Copy Table Schema Only

We can also use the SELECT INTO statement to create a new table with the given schema (without coping the data). For that, we use the WHERE clause with a condition that returns false.

SELECT *
INTO NewCustomers
FROM Customers
WHERE false;

Here, the SQL command creates an empty table named NewCustomers with the same structure as the Customers table.


Recommended Readings

Did you find this article helpful?