How to Copy Table Between Databases in MySQL

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

COPY TABLE BETWEEN DATABASES copies a table’s structure and/or data from one MySQL schema to another.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Why copy a table between databases?

Migrating data, splitting monolith schemas, or preparing test environments often requires moving an entire table—including indexes—into another database quickly.

Which methods work best?

The two fastest native approaches are: 1) CREATE TABLE newdb.tbl LIKE olddb.tbl then INSERT…SELECT; 2) mysqldump of a single table piped into mysql.Most teams use method 1 for in-server moves and method 2 for cross-server transfers.

What is the in-server syntax?

Run two statements: CREATE TABLE target_db.table LIKE source_db.table; INSERT INTO target_db.table SELECT * FROM source_db.table; Both execute on the same MySQL instance.

How do I copy structure only?

Skip the INSERT.The CREATE TABLE LIKE command duplicates columns, indexes, AUTO_INCREMENT, and default values but omits rows.

How do I copy data only?

If the destination table already exists with the desired schema, run INSERT INTO dest_db.table SELECT * FROM src_db.table; Rows append without touching structure.

How do I copy between different servers?

Use mysqldump: mysqldump -hsrc -uuser -p src_db table | mysql -hdst -uuser -p dst_db. This streams the CREATE TABLE and INSERT statements over the network.

Can I filter rows while copying?

Yes.Add a WHERE clause to INSERT…SELECT. Example: INSERT INTO dst_db.Orders SELECT * FROM src_db.Orders WHERE order_date < '2024-01-01';.

How to keep foreign keys valid?

Copy parent tables before child tables. Disable foreign_key_checks=0 during the process, then set it back to 1 after all INSERTs finish.

Best practices for large tables?

Copy during off-peak hours, add LIMIT…OFFSET batches, and monitor replica lag. For mysqldump, use --single-transaction to avoid locking innodb tables.

Quick recap

Inside one server, use CREATE TABLE LIKE + INSERT…SELECT. Across servers, stream with mysqldump.Always verify row counts and re-enable constraints.

Why How to Copy Table Between Databases in MySQL is important

How to Copy Table Between Databases in MySQL Example Usage


-- Move last year’s Orders to an archive schema
CREATE TABLE archive.Orders LIKE ecommerce.Orders;
INSERT INTO archive.Orders
SELECT *
FROM ecommerce.Orders
WHERE order_date < '2024-01-01';

How to Copy Table Between Databases in MySQL Syntax


-- Duplicate structure and data in the same MySQL instance
CREATE TABLE ecommerce_archive.Customers LIKE ecommerce.Customers;
INSERT INTO ecommerce_archive.Customers
SELECT * FROM ecommerce.Customers;

-- Structure only
CREATE TABLE analytics.Orders LIKE ecommerce.Orders;

-- Data only (table already exists)
INSERT INTO analytics.OrderItems (id, order_id, product_id, quantity)
SELECT id, order_id, product_id, quantity
FROM ecommerce.OrderItems;

-- Cross-server, single table
mysqldump -h prod-db -u root -p ecommerce Products \
| mysql -h reporting-db -u analyst -p analytics

Common Mistakes

Using CREATE TABLE…SELECT * without LIKE. This copies data but loses indexes, defaults, and AUTO_INCREMENT. Fix: run CREATE TABLE dst LIKE src first.
Forgetting to disable foreign_key_checks when child tables reference parents that haven’t been copied yet. Solution: SET foreign_key_checks=0 before copy, then re-enable.