Introduction:

When working with MySQL, there are times when we may need to copy a table from one database to another. This could be for backup purposes, moving data between environments (such as development and production), or simply reusing an existing structure. MySQL provides simple and effective ways to achieve this, either through SQL queries or command-line utilities.

Step1:

If we want to copy both the table structure and the data into another database:

$CREATE TABLE target_db.table_name LIKE source_db.table_name;
$INSERT INTO target_db.table_name SELECT * FROM source_db.table_name;
  • The first command creates an empty table with the same structure.
  • The second command copies all rows into the new table.

Step2:

If the table already exists in the target database and we only want to copy rows

$INSERT INTO target_db.table_name SELECT * FROM source_db.table_name;

Step3:

If we want to create a completely new table with the same structure and data in the target database:

CREATE TABLE target_db.new_table AS
SELECT * FROM source_db.table_name;

Step4:

If the databases are on different servers, you can use mysqldump:

$mysqldump -u user -p source_db table_name | mysql -u user -p target_db
  • The above command exports the table from one server and imports it into the target database.

Conclusion:

Copying a table from one database to another in MySQL is a straightforward process that can be done using SQL queries or command-line tools. For databases on the same server, CREATE TABLE ... LIKE and INSERT INTO ... SELECT These are the most efficient methods. If the databases are on different servers, mysqldump is a reliable option. By choosing the right approach.

Post Views: 1

Leave a Reply