The following is the syntax to copy a table from one database to another.
INSERT INTO yourDestinationDatabaseName.yourTableName SELECT * from yourSourceDatabaseName.yourtableName;
Let us see an example.
The CREATE command is used to create a table in the database ‘business’. We are creating a new table here.
mysql> use business; Database changed mysql> create table OriginalTable -> [ -> id int -> ]; Query OK, 0 rows affected [0.46 sec]
Creating a new table in the database ‘test’.
mysql> use test; Database changed mysql> create table OriginalTable -> [ -> id int -> ]; Query OK, 0 rows affected [0.47 sec
Records are inserted with the help of INSERT command in the first table.
mysql> use business; Database changed mysql> insert into OriginalTable values[1],[2],[3],[4]; Query OK, 4 rows affected [0.18 sec] Records: 4 Duplicates: 0 Warnings: 0
To display all the records.
mysql> select *from OriginalTable;
Here is the output.
+------+ | id | +------+ | 1 | | 2 | | 3 | | 4 | +------+ 4 rows in set [0.00 sec]
To copy the above table from database “business” into another database “test”.
mysql> INSERT INTO test.OriginalTable SELECT * from business.OriginalTable; Query OK, 4 rows affected [0.20 sec] Records: 4 Duplicates: 0 Warnings: 0
To check if the table has been copied.
mysql> use test; Database changed mysql> select *from OriginalTable;
The following output shows that the table “OriginalTable” have been copied successfully into the second database.
+------+ | id | +------+ | 1 | | 2 | | 3 | | 4 | +------+ 4 rows in set [0.00 sec]
Updated on 30-Jul-2019 22:30:23
- Related Questions & Answers
- How to copy rows from one table to another in MySQL?
- How to copy a collection from one database to another in MongoDB?
- Copy from one column to another [different tables same database] in MySQL?
- MySQL statement to copy data from one table and insert into another table
- Simplest way to copy data from one table to another new table in MySQL?
- MySQL query to copy records from one table to another with different columns
- Copy a few columns from a table to another in MySQL
- Copy column values from one table into another matching IDs in MySQL
- How to copy tables or databases from one MySQL server to another MySQL server?
- Move rows from one table to another in MySQL?
- Insert data from one table to another in MySQL?
- Copy all rows of a table to another table in MySQL?
- How to copy data from one field to another on every row in MySQL?
- How to copy files from one folder to another using Python?
- How to copy files from one server to another using Python?
Home / Copy a MySQL table with phpMyAdmin Last week I looked at how to copy a table with MySQL using some SQL queries and then on Sunday a
PHP script to automate the process. This post looks at how to copy a table with phpMyAdmin so you can easily do the same thing using a webpage GUI. Log into phpMyAdmin, select the database and then the table that you wish to copy. Click the "Operations" tab which appears in the tabs near the top of the page in the right frame. This tab is highlighted in the screenshot below with the red
arrow pointing to it.
The available operations appear underneath the tabs and there are various options. The one we are interested in with this post is the "Copy table to…" section which is highlighted with the red box.
You can either make a copy of the table to the current database, which is selected by default from the database drop down box, or copy it to another database for which you have the appropriate permissions.
After the drop down box is a dot [ . ] and then a textbox where you enter the name of the table to copy the structure and / or data into.
There are then three radio options:
- structure only: this will create a copy of the table with the name you specify but won’t copy any data over
- structure and data: this will do the above and then copy all data across using INSERT INTO … SELECT * FROM …
- data only: this only does the INSERT INTO … SELECT * FROM … query
You can also select whether to drop the table that you are copying to before creating the table and copying the data. This runs a DROP TABLE IF EXISTS query for the copy of the table, not the original. It has no effect if selected when doing a data only copy.
Another option is to copy the auto increment value. By default this is not done and the auto increment value will end up being whatever it would be after the INSERT INTO query is done. If checked then the CREATE TABLE syntax will include the auto increment value from the original table.
The final checkbox is to change to the copied table after the queries are done. If unchecked the resulting page will still be looking at the original table; if checked it will change to the copied database [if applicable] and table.