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;
