MySQL Copy Table With Examples

MySQL Copy Table With Examples

Copying a table in MySQL can be useful for backups, testing, and schema duplication. There are multiple ways to copy a table, depending on whether you want to copy only the structure or both structure and data.

1. Copy Table Structure Only

To duplicate a table without copying data, use:

CREATE TABLE new_table LIKE original_table;

✅ This copies the table structure, including columns, indexes, and constraints (except FOREIGN KEY constraints).

Example

CREATE TABLE employees_backup LIKE employees;

✅ This creates an empty employees_backup table with the same structure as employees.

2. Copy Table Structure and Data

To copy both structure and data, use INSERT INTO ... SELECT *:

CREATE TABLE new_table LIKE original_table;
INSERT INTO new_table SELECT * FROM original_table;

Example

CREATE TABLE employees_backup LIKE employees;
INSERT INTO employees_backup SELECT * FROM employees;

✅ This duplicates the a employees table along with all records.

3. Copy Table with Selected Columns Only

If you want to copy specific columns, specify them in the SELECT statement:

CREATE TABLE new_table AS
SELECT column1, column2 FROM original_table;

Example

CREATE TABLE employees_backup AS
SELECT id, name, department FROM employees;

✅ This copies only id, name, and department columns.

4. Copy Table with a Condition

To copy only certain records, add a WHERE clause:

CREATE TABLE new_table AS
SELECT * FROM original_table WHERE condition;

Example

CREATE TABLE active_employees AS
SELECT * FROM employees WHERE status = 'active';

✅ This copies only active employees.

5. Copy Table Without AUTO_INCREMENT Values

To reset the AUTO_INCREMENT column in the copied table:

CREATE TABLE new_table LIKE original_table;
ALTER TABLE new_table AUTO_INCREMENT = 1;
INSERT INTO new_table SELECT * FROM original_table;

✅ This ensures new IDs start from 1.

6. Copy Table and Rename Columns

To rename columns in the copied table:

CREATE TABLE new_table AS
SELECT column1 AS new_column1, column2 AS new_column2 FROM original_table;

Example

CREATE TABLE employee_archive AS
SELECT id AS emp_id, name AS emp_name FROM employees;

✅ This copies data while renaming columns.

7. Copy Table with Indexes and Constraints

To copy indexes and constraints along with data, use:

CREATE TABLE new_table LIKE original_table;
ALTER TABLE new_table DISABLE KEYS;
INSERT INTO new_table SELECT * FROM original_table;
ALTER TABLE new_table ENABLE KEYS;

✅ Disabling keys speeds up insertion for large datasets.

8. Copy Table to Another Database

To copy a table from one database to another:

CREATE TABLE new_db.new_table LIKE old_db.original_table;
INSERT INTO new_db.new_table SELECT * FROM old_db.original_table;

Example

CREATE TABLE backup_db.employees_backup LIKE company_db.employees;
INSERT INTO backup_db.employees_backup SELECT * FROM company_db.employees;

✅ Copies employees from company_db to backup_db.

9. Conclusion

Copy Type	Method
Only structure	CREATE TABLE new_table LIKE original_table;
Structure + Data	CREATE TABLE new_table LIKE original_table; INSERT INTO new_table SELECT * FROM original_table;
Specific Columns	CREATE TABLE new_table AS SELECT column1, column2 FROM original_table;
With Conditions	CREATE TABLE new_table AS SELECT * FROM original_table WHERE condition;
With Reset AUTO_INCREMENT	ALTER TABLE new_table AUTO_INCREMENT = 1;
Copy to Another Database	CREATE TABLE new_db.new_table LIKE old_db.original_table; INSERT INTO new_db.new_table SELECT * FROM old_db.original_table;

🚀 Choose the right method to efficiently copy MySQL tables!