Copy table from different database mysql

Question

In this post, we are going to show you how to copy a table in MySQL.

Table of Contents Show

IGNORE Clause
Copying from One Table to the Next
Register to view this lesson
Unlock Your Education
Resources created by teachers for teachers
How do I copy a table from one MySQL database to another?
How do I copy a table data from one database to another database?
How do I move a table from one database to another in MySQL WorkBench?
How do I clone a table in MySQL?

First, this query will copy the data and structure, but the indexes are not included:

CREATE TABLE new_table SELECT * FROM old_table;

Second, this query will copy the table structure and indexes, but not data:

CREATE TABLE new_table LIKE old_table;

So, to copy everything, including database objects such as indexes, primary key constraint, foreign key constraints, triggers, etc., run these queries:

CREATE TABLE new_table LIKE old_table; 
INSERT new_table SELECT * FROM old_table;

If you want to copy a table from one database to another database:

CREATE TABLE destination_db.new_table LIKE source_db.old_table;

INSERT destination_db.new_table
SELECT
    *
FROM
    source_db.old_table;

Using TablePlus, you can use the Export and Import Wizard to duplicate one table as well as multiple tables.

If you want to copy the creation of the table using TablePlus, you can do it in two ways:

Switch to the structure tab from the table view (Cmd + Shift + ]), then click on the Definition button near the top right to see the CREATE TABLE statement.
Or you can install the Dump Table plugin: press Cmd + L to open plugin manager, then install Dump Table. After you install it successfully, you can right-click on a table and choose Copy Creation.

Need a good GUI Tool for MySQL? TablePlus is a modern, native tool with an elegant UI that allows you to simultaneously manage multiple databases such as MySQL, PostgreSQL, SQLite, Microsoft SQL Server and more.

Download TablePlus for Mac. It’s free anyway!

Not on Mac? Download TablePlus for Windows.

On Linux? Download TablePlus for Linux

Need a quick edit on the go? Download TablePlus for iOS.

You can duplicate or "clone" a table's contents by executing a CREATE TABLE ... AS SELECT statement:

CREATE TABLE new_table AS SELECT * FROM original_table;

Please be careful when using this to clone big tables. This can take a lot of time and server resources.

Note also that new_table inherits ONLY the basic column definitions, null settings and default values of the original_table. It does not inherit indexes and auto_increment definitions.

To inherit all table definitions, use the CREATE TABLE... LIKE syntax:

CREATE TABLE new_table LIKE original_table;

This makes the structure of new_table exactly like that of original_table, but DOES NOT copy the data. To copy the data, you'll need INSERT ... SELECT:

INSERT INTO new_table SELECT * FROM original_table;

Again, be careful when doing this to big tables.

Instructor: Martin Gibbs Show bio

Martin has 20 years experience in Information Systems and Information Technology, has a PhD in Information Technology Management, and a master's degree in Information Systems Management. He is an adjunct professor of computer science and computer programming.

In MySQL, data can be copied between tables using a combination of commands. Explore the proper syntax using INSERT, SELECT, and IGNORE to specify data when copying between tables and even new databases. Updated: 12/28/2021

Databases can become huge and unwieldy very quickly. Demands on database administrators are great; not only do you have to provide a huge amount of data to customers, but you also need to maintain that data so that it's manageable.

Copying data between tables is useful to reduce the size of tables, organize data more efficiently, or create temporary copies of data for specific queries. For example, if you wanted to copy all data about Florida customers to a separate table for analysis, MySQL provides an efficient way to do this. This not only preserves the original data, but it keeps the source table data separate and secure.

You can copy data from one table to another table (even if that table doesn't yet exist). Data can also be copied from a table in one database to another database.

MySQL provides a powerful option for copying data from one table to another table (or many tables).

The basic command is known as INSERT ... SELECT.

A full layout of the syntax is shown below:

INSERT [IGNORE]
  [INTO] table_name
  [(column_name, ...) ]
SELECT ...
FROM table_name WHERE ...

Note that both tables must exist. You would need to use CREATE TABLE to create either or both of the tables; however, this lesson will cover a cleaner way of accomplishing this.

Before we get into some of the specifics, let's look at a straightforward example of the statement: We will copy shopping cart data from one table to another, but only where the customer location is Florida. This will help maintain the Florida customers separately.

INSERT INTO shopping_cart_Florida (customer_name, customer_State, orderID)
SELECT shopping_cart.orderID
FROM shopping_cart WHERE shopping_cart.customerState = 'FL';

It's important to note that all statements end in a semicolon. We're showing line breaks for readability, but MySQL only looks for the semicolon to know that it is done processing that command. In subsequent examples in this lesson, there will be multiple lines; note the semicolon that denotes the end of the command.

Video
Quiz
Course

IGNORE Clause

The INSERT ... SELECT syntax provides an IGNORE option. This will ignore (not copy) rows of data that could possibly create a duplicate key. This is important because we would not want to create several rows in the target table that are duplicates. This creates a mess for querying and data integrity.

Thus, the previous code would look like:

INSERT IGNORE INTO shopping_cart_Florida (customer_name, customer_State, orderID)
SELECT shopping_cart.orderID
FROM shopping_cart WHERE shopping_cart.customerState = 'FL';

Copying from One Table to the Next

The previous example showed us how to copy from one table to another. There is a slight problem with that approach: we have to assume that the shopping_cart_Florida table already exists. In order to use the code, we would have needed to use the CREATE TABLE command. While it works just fine, there is an option to do all this work in one pass:

CREATE TABLE IF NOT EXISTS shopping_cart_Florida LIKE shopping_cart;

Register to view this lesson

Are you a student or a teacher?

Unlock Your Education

See for yourself why 30 million people use Study.com

Become a Study.com member and start learning now.

Become a Member

Already a member? Log In

Back

Resources created by teachers for teachers

Over 30,000 video lessons & teaching resources‐all in one place.

Video lessons

Quizzes & Worksheets

Classroom Integration

Lesson Plans

I would definitely recommend Study.com to my colleagues. It’s like a teacher waved a magic wand and did the work for me. I feel like it’s a lifeline.

Back

Create an account to start this course today

Used by over 30 million students worldwide

Create an account

How do I copy a table from one MySQL database to another?

The fastest way to copy a table in MySQL:.

Right-click the table you want to copy in Database Explorer and select Duplicate Object..

In the dialog that opens, select the destination db..

Select to copy the table data or structure only..

Specify the name of the new table, and click OK..

How do I copy a table data from one database to another database?

Right-click on the database name, then select "Tasks" > "Export data..." from the object explorer. The SQL Server Import/Export wizard opens; click on "Next". Provide authentication and select the source from which you want to copy the data; click "Next". Specify where to copy the data to; click on "Next".