How to insert data from one server database table to another server database table in mysql

How to Copy Table in MySQL: Duplicate Structure, Data, and Indexes Correctly

Copying tables in MySQL is a routine operation performed by DBAs, developers, and analysts dozens of times a day for various reasons and with various purposes. In this tutorial, we provide a detailed overview of the most common methods to copy a MySQL table structure and data.

Read the guide to find out:

• Ways to copy MySQL table definition in MySQL
• SQL examples to copy a table in MySQL
• How to copy table data and structure to another database
• How to duplicate a MySQL table including indexes and data
• How to insert table data into an already existing table
• The fastest way to copy a table—by using the MySQL GUI tool

How to copy a table in MySQL

MySQL doesn't have the copy table statement, which means you'll have to use sideways approaches to perform the operation. There are three popular ways to clone a table in MySQL.

1. CREATE TABLE ... AS SELECT statement to copy the source table column attributes and data.

CREATE TABLE new_table_name
AS
SELECT 
  * 
FROM old_table_name;
                                    

2. CREATE TABLE ... LIKE statement to create an empty table based on the definition of the original table, including column attributes and indexes.

CREATE TABLE new_table_name LIKE old_table_name;
                                    

3. SHOW CREATE TABLE to generate a create table script for the original table.

SHOW CREATE TABLE customer\G
                                    

MуSQL copy table structure only

Let's now take a closer look at ways to create a table in MySQL by using SQL statements.

If you need to duplicate the table structure, but not its data, it is better to use the CREATE TABLE ... LIKE statement.

Suppose, we need to clone the structure of the customer table. The query to copy structure is as follows:

CREATE TABLE customer_new LIKE customer
                                    

In case you need to copy the table structure including primary keys, foreign keys and constraints, run the SHOW CREATE TABLE ... statement, then copy the script for the original table, change the name of the table and execute the script.

Copy table data to another table

To copy data from one table to another, use the INSERT INTO statement. Note, that you can specify the columns to be copied.

INSERT INTO target_table (`column1`, `column2`)
  SELECT
    `column1`,
    `column2`
  FROM source_table;
                                    

This method works perfectly well if you have already copied the table structure like we did in the example above and now need to copy values.

In case you want to duplicate all rows from the source table, use the following syntax:

CREATE TABLE new_table_name LIKE old_table_name; 
INSERT new_table_name SELECT * FROM old_table_name;
                                    

Copy a table from one database to another

In MySQL, the easiest way to copy a table with its data between two databases is to use the CREATE TABLE AS statement, but note, that you need to provide the target database name as a table prefix.

CREATE TABLE new-database-name.new-table-name
AS
SELECT * FROM old-database.old-table-name;
                                    

If you need to copy to another database schema only the table structure, use the CREATE TABLE LIKE statement, but again don't forget to specify the database names. Remember that by a schema in MySQL is typically meant a system schema. Simply put, a schema in MySQL corresponds to the concept of a database in SQL Server.

CREATE TABLE new-database-name.new-table-name
LIKE old-database.old-table-name;
                                    

However, by running the query above, you will create a clone of the source table with all the column attributes, indexes, and keys. To copy the table structure only, you can run use a LIMIT clause in the query to make MySQL left aside the data.

CREATE TABLE new-database-name.new-table-name 
SELECT 
  * 
FROM from old-database.old-table-name LIMIT 0;
                                    

Copy a table to a new table: Example

In MySQL, you don't have to copy the entire table, you can copy the specific columns only. For this, you can use the CREATE TABLE and the SELECT statement as shown below:

CREATE TABLE new_table_name 
SELECT column1, column2, column3 
FROM
    old_table_table;
                                    

Suppose, we want to create a new table film_copy which will consist of three columns: film_id, film_title, and film_description.

CREATE TABLE film_copy
SELECT
  f.film_id,
  f.title,
  f.description
FROM film f;
                                    

The fastest way to copy a table in MySQL:dbForge Studio for MySQL

dbForge Studio for MySQL delivers a quick and easy way to copy a table, even a large one, without coding—via the comprehensive and user-friendly graphic interface.

Step 1. Right-click the table you want to copy in Database Explorer and select Duplicate Object.
Step 2. In the dialog that opens, select the destination db.
Step 3. Select to copy the table data or structure only.
Step 4. Specify the name of the new table, and click OK.

Still want to create table by writing SQL code?
dbForge Studio has a lot to offer in that case too. Its advanced SQL Editor comes with sophisticated code completion, robust syntax check, instant code formatter, and dozens other features invaluable for hand coding.

The fastest and the easiest way to create a table in MySQL is definitely by using dbForge Studio for MySQL!

With dbForge Studio for MySQL, you also GET

• Advanced MySQL Debugger
• Robust Query Profiler
• Visual query builder
• Security Manager

• Data export and import tools
• MySQL schema and data compare functionality
• Pivot tables utility
• Report designer

Conclusion

In this article, we have demonstrated the most popular ways to create a duplicate table in MySQL: by different SQL statements and by using an all-in-one IDE for MySQL database development, management, and administration—dbForge Studio that allows you to perform the any database-related tasks in a GUI. Within just a few clicks, you can rename a MySQL table, perform MySQL database migration, duplicate a MySQL database, and much, much more.

Unleash your database development potential

Availability in the editions of dbForge Studio for MySQL

Feature

Enterprise

Professional

Standard

Express

How will you insert data from one server database table to another server database table in SQL Server?

How will you insert data from one database table to another database table in MySQL server?

How do I copy data from one database table to another database table in SQL Server 2008?

How do I transfer data from one MySQL server to another?