Duplicate a MySQL table with Data

To duplicate a MySQL table with data, you can use the CREATE TABLE statement with the AS SELECT clause. This will create a new table with the same structure and data as the original table.

Duplicate a MySQL table with Data

Here’s an example of how to duplicate a table called employees:

CREATE TABLE employees_copy AS SELECT * FROM employees;

This will create a new table called employees_copy with the same columns and data as the employees table. Note that this will not include any indexes or constraints from the original table.

If you want to include indexes and constraints in the new table, you can use the SHOW CREATE TABLE statement to generate the SQL code for the original table, modify it as needed, and then execute it to create the new table.

Here’s an example of how to duplicate a table with indexes and constraints:

-- Generate the SQL code for the original table
SHOW CREATE TABLE employees;

-- Copy the SQL code and modify it to create a new table
CREATE TABLE employees_copy (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    email VARCHAR(255),
    created_at DATETIME,
    updated_at DATETIME,
    INDEX idx_name (name),
    CONSTRAINT fk_dept FOREIGN KEY (dept_id) REFERENCES departments(id)
);

This will create a new table called employees_copy with the same structure, indexes, and constraints as the employees table, and then copy the data from the original table into the new table.

Copy Data From the Original Table

Finally, You can now use the command below to copy data from the original table into the new table. To copy the data from the original table into the new table, you can use the INSERT INTO statement with the SELECT clause. Here’s an example:

INSERT INTO employees_copy SELECT * FROM employees;

This statement will insert all the rows from the employees table into the employees_copy table. Note that the columns in the original table must match the columns in the new table for this to work. If the columns are not in the same order, you can specify the column names in the INSERT INTO and SELECT statements:

INSERT INTO employees_copy (id, name, email, created_at, updated_at)
SELECT id, name, email, created_at, updated_at FROM employees;

This will insert the id, name, email, created_at, and updated_at columns from the employees table into the corresponding columns in the employees_copy table.

If you only want to copy a subset of the rows from the original table, you can add a WHERE clause to the SELECT statement:

INSERT INTO employees_copy SELECT * FROM employees WHERE dept_id = 1;

This will insert only the rows from the employees table where the dept_id column is equal to 1 into the employees_copy table.