How To Copy Table Data in MySQL? [Full Reference]

Copy Table Data In MySQL

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;

To use a particular database from the list of available databases:

USE databasename;

To see all the tables present in the database:

SHOW TABLES;

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;

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;

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;

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;

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;

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