Duplicate a table from a database into another table in another database

Posted on

This is an article used to show an experiment on duplicating a single table from a database into another table in another database specifically in MySQL Database Server.

The scenario above is done in order to try to compare whether a similar table which has a denormalized form in MyISAM engine is a lot faster to be selected rather than in InnoDB engine.

But in this article, the focus of the description is only limited into the duplication process. Below is a case example of achieving it :

The current condition :

previous-condition-db

The condition which is going to be achieved :

Duplicate-a-table-from-a-database-into-another-table-in-another-database
Duplicate a table from a database into another table in another database

So, to achieve it, check the source database and select it first :

mysql> use testing;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql>

Show the tables exist in the source database so that one of it can be picked to be duplicate into another database by typing the following command in MySQL command console :

show tables

Below is the output of the command execution :

mysql> show tables;
+-------------------+
| Tables_in_testing |
+-------------------+
| CUSTOMER          |
| user              |
+-------------------+
2 rows in set (0,00 sec)

mysql>
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql>

After that, show the available table in the destination database by executing the same command as the previous one, ‘show tables’ :

mysql> show tables;

Check also the destination table and select it first :

+----------------+
| Tables_in_test |
+----------------+
| account        |
+----------------+
1 row in set (0,00 sec)

mysql> 

After doing the above steps, the following is the command which is used to duplicate table named ‘user’ from database ‘test’ to database ‘testing’ :

create table db_destination.table_destination like db_source.table_source

Below is the execution of the above query pattern in MySQL command line if it is executed to achieve the goal :

mysql> create table test.user like testing.user;
Query OK, 0 rows affected (0,19 sec)

mysql>

After successfully duplicate the table structure, the next step is to copy the entire data by executing the following command :

insert into db_destination.table_destination select * from db_source.table_source

Below is the command execution based on the scenario :

mysql> insert into test.user select * from testing.user;
Query OK, 4 rows affected (0,07 sec)
Records: 4  Duplicates: 0  Warnings: 0
mysql>

Check the content of the table :

mysql> select * from test.user;
+----+----------+-------------------------------------------+
| id | username | password                                  |
+----+----------+-------------------------------------------+
|  1 | james    | *2F27A7A3F5C973EC203A5380F414C56C85FAF545 |
|  2 | mark     | *2F27A7A3F5C973EC203A5380F414C56C85FAF545 |
|  3 | steve    | *2F27A7A3F5C973EC203A5380F414C56C85FAF545 |
|  4 | jack     | *2F27A7A3F5C973EC203A5380F414C56C85FAF545 |
+----+----------+-------------------------------------------+
4 rows in set (0,00 sec)

mysql>

The scenario above has already been successfully executed.

Leave a Reply