MySQL Copy Table

Last updated: October 19, 2026Oct 19, 2026 5 min read

Copying a table is useful for backups before a risky change, for test data, for archiving, and for experimenting without touching the original. MySQL has no single COPY TABLE command, but a few statements together let you copy exactly what you need: structure only, data only, or both.

1. The Source Table

Table: products (5 rows)

2. Method 1: Full Copy with LIKE + INSERT … SELECT

This is the most faithful copy. LIKE copies the exact definition (columns, defaults, primary key, unique and normal indexes, AUTO_INCREMENT); INSERT … SELECT copies the rows:

CREATE TABLE products_backup LIKE products;
INSERT INTO products_backup SELECT * FROM products;

SELECT COUNT(*) AS copied_rows FROM products_backup;
SHOW INDEX FROM products_backup;

Result: 1 row

Result: 3 rows

3. Method 2: Quick Copy with CREATE TABLE … AS SELECT

CREATE TABLE products_snapshot AS SELECT * FROM products;
SHOW CREATE TABLE products_snapshot;

Result: 1 row

The columns and rows are there, but there is no primary key, no unique key, no index and no AUTO_INCREMENT. That's fine for a read-only snapshot or report, but not for a table your application will keep using.

4. Copying Only the Structure

CREATE TABLE products_template LIKE products;
SELECT COUNT(*) AS rows_in_template FROM products_template;

Result: 1 row

An older trick, CREATE TABLE t AS SELECT * FROM products WHERE 1 = 0;, also gives an empty table, but without keys and indexes.

5. Copying Some Rows or Columns

Add a WHERE clause, pick columns, or even calculate new values while copying:

CREATE TABLE snack_prices AS
SELECT sku, name, price, ROUND(price * 0.9, 2) AS sale_price
FROM products
WHERE category = 'Snacks';

SELECT * FROM snack_prices;

Result: 2 rows

To add more rows to an existing copy, use INSERT … SELECT with a column list:

INSERT INTO snack_prices (sku, name, price, sale_price)
SELECT sku, name, price, ROUND(price * 0.85, 2) FROM products WHERE category = 'Pantry';
SELECT * FROM snack_prices;

Result: 3 rows

6. Re-running a Copy Without Duplicates

Running INSERT … SELECT again into a table with a primary key fails on the first duplicate:

INSERT INTO products_backup SELECT * FROM products;

ERROR 1062 (23000): Duplicate entry '1' for key 'products_backup.PRIMARY'

Use INSERT IGNORE to skip existing rows, or an upsert to refresh them:

UPDATE products SET price = 99 WHERE sku = 'BIS-03';

INSERT INTO products_backup SELECT * FROM products AS src
ON DUPLICATE KEY UPDATE price = src.price;

SELECT sku, price FROM products_backup WHERE sku = 'BIS-03';

Result: 1 row

7. Copying to Another Database

CREATE DATABASE reporting;
CREATE TABLE reporting.products LIKE shopdb.products;
INSERT INTO reporting.products SELECT * FROM shopdb.products;
SELECT COUNT(*) AS rows_in_reporting FROM reporting.products;

Result: 1 row

8. Copying to Another Server

Between servers, export and import with mysqldump:

# one table: structure + data
mysqldump -u root -p shopdb products > products.sql
mysql -h other-server -u root -p targetdb < products.sql

# or stream it directly, without a file
mysqldump -u root -p shopdb products | mysql -h other-server -u root -p targetdb

9. Copying Large Tables

• INSERT … SELECT on a huge table runs as one big transaction; copy in batches by primary-key range (WHERE id BETWEEN 1 AND 100000, then the next range).
• Create the copy's secondary indexes after loading the data, which is often faster than loading into an indexed table.
• The copy needs as much disk space as the original; check free space first.

10. Which Method to Use

11. Practice Exercises

CREATE TABLE products_v2 LIKE products;
INSERT INTO products_v2 SELECT * FROM products WHERE category = 'Beverages';
SELECT sku, name FROM products_v2;

CREATE TABLE category_summary AS
SELECT category, COUNT(*) AS items, ROUND(AVG(price), 2) AS avg_price
FROM products GROUP BY category;
SELECT * FROM category_summary;

12. Summary

• Full copy: CREATE TABLE … LIKE + INSERT … SELECT.
• CREATE TABLE … AS SELECT is quick but loses keys, indexes and AUTO_INCREMENT.
• Filter rows, choose columns or compute values while copying.
• Use INSERT IGNORE or ON DUPLICATE KEY UPDATE when copying again.
• Qualify names to copy across databases; use mysqldump or MySQL Shell across servers.