SQL SELECT INTO (Copy Table)

In SQL, the SELECT INTO statement is used to copy data from one table to another.

Example

-- copy all the contents of a table to a new table
SELECT *
INTO CustomersCopy
FROM Customers;

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


SELECT INTO Syntax

The syntax of the SQL SELECT INTO statement is:

SELECT column1, column2, column3, ...
INTO destination_table
FROM source_table;

Here,

  • column1, column2, column3, ... are the columns to be copied
  • destination_table is the new table where the data is to be copied to
  • source_table is the table where the data is to be copied from

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 you want to copy data to an existing table (rather than creating a new table), you should use the INSERT INTO SELECT statement.


Copy Selected Columns Only

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

-- copy selected columns only
SELECT customer_id, country
INTO CustomersCountry
FROM Customers;

Here, the SQL command only copies the customer_id and country columns to the CustomersCountry 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,

-- copy rows where country is USA
SELECT customer_id, age
INTO USACustomersAge
FROM Customers
WHERE country = 'USA';

Here, the SQL command

  • creates the USACustomersAge table with customer_id and age columns
  • copies rows from the Customers 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,

-- copy contents of a table to another database
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,

-- copy rows from Customers and Orders tables
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 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 structure (without copying the data). For that, we use the WHERE clause with a condition that returns false.

-- copy table structure only
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.


Also Read:

Did you find this article helpful?

Our premium learning platform, created with over a decade of experience and thousands of feedbacks.

Learn and improve your coding skills like never before.

Try Programiz PRO
  • Interactive Courses
  • Certificates
  • AI Help
  • 2000+ Challenges