In SQL, the
SELECT INTO statement is used to copy data from one table to another.
-- 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;
column1, column2, column3, ...are the columns to be copied
destination_tableis the new table where the data is to be copied to
source_tableis the table where the data is to be copied from
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
Customerstable if the value of the country column is USA
Copy to Another Database
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
-- 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.