How to Copy MySQL Table from One Database to Another Database

Do you want to copy tables from one database to another in MySQL? If yes, this is the right blog for you. The need to copy tables from one database to another may appear in situations such as maintenance, testing, performance, migration, transfer to a different instance, and more. This is easy to do and can be done in many ways.

In this article, I’ll walk-through several ways to copy tables from one database to another in MySQL Server, helping you to see the benefits and trade-offs of each method. So, without wasting any more time, let’s start!

Method #1: Copy Table from one database to another in MySQL

Copying tables in a MySQL database to another MySQL database on the same server is quite easy. To copy data from a table to a new table, you can use CREATE TABLE and SELECT statements as follows:

First, create the table using the same engine and indexes as the original table:

CREATE TABLE dbtwo.mytable

LIKE dbone.mytable;

• Next, copy all the records from the original table into the new table:

INSERT INTO dbtwo.mytable

SELECT *

FROM dbone.mytable;

• You can also use the syntax:

CREATE TABLE table2 AS SELECT * FROM table1;

Note: To speed up inserts on the table with many records and keys, you can first disable the keys, then enable them:

ALTER TABLE dbtwo.mytable

DISABLE KEYS;

# do inserts here

ALTER TABLE dbtwo.mytable

ENABLE KEYS;

Keep in mind that the queries above only copy the table and its data. It does not copy other database objects such as index, primary key constraint, foreign key constraints, triggers, etc. associated with the table.

Method #2: Copy MySQL table from one database to another

You may go for the second method when the above methods fail to work properly. The queries mentioned above does not copy the database objects. Don’t worry! I have got a better solution for this. 

You should go for the professional software and one such tool is Aryson MySQL to MSSQL Converter. The tool offers a highly reliable solution to copies your MySQL tables with indexes and keys from one database to another within the same MySQL server.

It is a one-stop solution for copying tables from one database to another in MySQL server. The software has the proficiency to convert selected or significant Database objects such as tables into a specified database file format. I would recommend you to try the free demo version of this utility.

Steps to Copy MySQL Table from One Database to Another

Concluding Lines

I hope now you must have got a fair idea about how to copy tables from one database to another in MySQL. Two of the best methods are mentioned above but it is best to go with the MySQL to SQL Converter tool for migrating the tables with all its objects. For any such queries keep reading this column.

FAQ

Realted Post