Copy data into new table in MySQL

Last updated on September 11th, 2020 at 11:23 am

You can easily copy data into new table in MySQL using SELECT INTO. This helps you create temporary tables quickly.

The SELECT INTO statement copies data from one table and inserts it into a new table. The SELECT INTO statement selects data from one table and inserts it into a new table.

SQL SELECT INTO Syntax

Copy all columns into new table in same database:

SELECT *
INTO new_table
FROM table_name;

Copy all columns into new table in different database:

SELECT *
INTO new_table IN database_name
FROM table_name;

Copy only the columns we want into the new table in same database:

SELECT column_name1,column_name2,..
INTO new_table
FROM table_name;

SELECT column_name1,column_name2,..
INTO new_table IN database_name
FROM table_name;

Note

The new table will be created with the column-names and types as defined in the SELECT statement. You can apply new names using the AS clause.

SQL SELECT INTO Examples
Create a backup copy of Customers:

SELECT *
INTO Users_Backup
FROM Users;

Use the IN clause to copy the table into another database:

SELECT *
INTO Users_Backup IN 'Backup_database'
FROM Users;

Copy only a few columns into the new table:

SELECT User_Name, User_Contact
INTO Users_Backup
FROM Users;

Copy only the American customers into the new table:

SELECT *
INTO Users_Backup
FROM Users
WHERE Country='USA';

Copy data from more than one table into the new table:

SELECT Users.User_Name, Orders.Order_ID
INTO Users_Order_Backup
FROM Users
LEFT JOIN Orders
ON Users.User_ID=Orders.User_ID;

Tip: The SELECT INTO statement can also be used to create a new, empty table using the schema of another. Just add a WHERE clause that causes the query to return no data:

SELECT *
INTO new_table
FROM table_name
WHERE 1=0;