Copying data from one MySQL table to another seems easy but it’s a little tricky. In this tutorial, we’ll learn how to easily copy one table’s data to a new table, how to copy particular columns to another table, how to store a table of one database in another database, and so on.
Why do we need to clone table data?
Copying table data or cloning table data helps in ways such as backup and recovery or testing the database without changing the original databases.
Copying Table Data to a New Table Within the Same Database in MySQL
Step 1: Open MySQL Command Line Client and search for the database you have that table you want to clone.
To see all the available databases:
SHOW DATABASES;Code language: SQL (Structured Query Language) (sql)To use a particular database from the list of available databases:
USE databasename;Code language: SQL (Structured Query Language) (sql)To see all the tables present in the database:
SHOW TABLES;Code language: SQL (Structured Query Language) (sql)In our example, we’ll be using a table named sample which has 4 columns, Num, Name, Amount, and Place.
Step 2: Creating a table and copying all the values from the existing table.
CREATE TABLE tablename
SELECT *
FROM existingtable;Code language: SQL (Structured Query Language) (sql)Copying Partial Data From a New Table to a New Table Within the Same Database in MySQL
You must know in which database your table resides and then we’ll copy only copy particular columns to a new table within the same database. you can follow step 1 of the previous topic to know your existing table.
Now, let’s get to the command from which we can copy only specific columns and leave the rest.
CREATE TABLE tablename
SELECT column1, column2
FROM existingtable;Code language: SQL (Structured Query Language) (sql)In the above command, first, you must create a table and give it an appropriate name after that you must select all the columns you want in your new table. You can also add only one column if you wish or add more than two, it’s all up to your requirements.
Copying an Entire Table Data to an Existing Table Within the Same Database
When a table already exists in your database where you can copy the data of a certain table, then we use INSERT INTO command. Let’s see the command below:
INSERT INTO tablename
SELECT *
FROM existingtable;Code language: SQL (Structured Query Language) (sql)By using the above command, you’ll be able to copy the entire table to the table which already exists. Remember, this table must be specified with the columns of the actual table. Otherwise, MySQL will throw an error.
Copying Specific Columns of Table to an Existing Table Within the Same Database
Here, you must have specified the column names which you want to copy in the new table. If not then MySQL will throw an error. the following is the command to copy the table.
INSERT INTO tablename
SELECT column1, column2
FROM existingtable;Code language: SQL (Structured Query Language) (sql)In the above example, you can specify one or more columns as per your requirements.
For using the INSERT INTO command, you must have an existing table in your database otherwise MySQL will throw an error and all the columns which are being copied must be specified prior to the copy command.
Copying Table Data From One Database to Another Database
If you want to copy a table from database A to database B, then you can use the following command. For doing that you just need to specify the destination database name and the name of the table which you want to create and join them with a dot(.) operator.
CREATE TABLE databasename.tablename
SELECT *
FROM existingtable;Code language: SQL (Structured Query Language) (sql)You may use the INSERT INTO command as well, the condition being the table must already exist on the database you want to copy the table data to.
Conclusion
In this tutorial, we learned how to copy the full or partial data of a table to another table within the same database or a different database.
Useful Resources:
https://dev.MySQL.com/doc/refman/8.0/en/insert-select.html