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
- 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
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