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.
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
- creates the USACustomersAge table with customer_id and age column
- copies the rows to the new table 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,
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.