SQL SELECT INTO (Copy Table)

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

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

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:

• SQL CREATE TABLE
• SQL INSERT INTO