MySQL COPY TABLE

Introduction

In this chapter, we will learn how to copy a table in MySQL. Copying a table can be useful for creating backups, duplicating data for testing purposes, or creating similar tables with slight modifications. We will cover various methods to copy a table, including copying the structure only, copying the structure and data, and copying data to an existing table.

Copying a Table Structure Only

To copy only the structure of a table (i.e., the schema), you can use the CREATE TABLE ... LIKE statement. This creates a new table with the same structure as the existing table, but without any data.

Syntax

CREATE TABLE new_table_name LIKE existing_table_name;

• new_table_name: The name of the new table.
• existing_table_name: The name of the existing table whose structure you want to copy.

Example

CREATE TABLE employees_backup LIKE employees;

This example creates a new table named employees_backup with the same structure as the employees table.

Copying a Table Structure and Data

To copy both the structure and the data of a table, you can use the INSERT INTO ... SELECT statement. This creates a new table with the same structure and copies all the data from the existing table.

Syntax

CREATE TABLE new_table_name AS SELECT * FROM existing_table_name;

• new_table_name: The name of the new table.
• existing_table_name: The name of the existing table whose structure and data you want to copy.

Example

CREATE TABLE employees_copy AS SELECT * FROM employees;

This example creates a new table named employees_copy with the same structure and data as the employees table.

Copying Data to an Existing Table

If you want to copy data from one table to an existing table, you can use the INSERT INTO ... SELECT statement without the CREATE TABLE part. This is useful when you want to merge data from one table into another.

Syntax

INSERT INTO existing_table_name SELECT * FROM source_table_name;

• existing_table_name: The name of the existing table where you want to copy the data.
• source_table_name: The name of the source table from which you want to copy the data.

Example

INSERT INTO employees_copy SELECT * FROM employees;

This example copies all data from the employees table to the existing employees_copy table.

Full Example

Let's go through a full example where we create a table, copy its structure, copy its structure and data, and copy data to an existing table.

1. Create a Database:

CREATE DATABASE company;

2. Select the Database:

USE company;

3. Create a Table:

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    hire_date DATE
);

4. Insert Data into the Table:

INSERT INTO employees (first_name, last_name, email, hire_date) VALUES ('Rahul', 'Sharma', 'rahul.sharma@example.com', '2023-07-01');
INSERT INTO employees (first_name, last_name, email, hire_date) VALUES ('Priya', 'Singh', 'priya.singh@example.com', '2023-07-02');

5. Copy the Table Structure Only:

CREATE TABLE employees_backup LIKE employees;

6. Copy the Table Structure and Data:

CREATE TABLE employees_copy AS SELECT * FROM employees;

7. Verify the Data in the Copied Table:

SELECT * FROM employees_copy;

Output

id	first_name	last_name	email	hire_date
1	Rahul	Sharma	rahul.sharma@example.com	2023-07-01
2	Priya	Singh	priya.singh@example.com	2023-07-02

Important Considerations

• Constraints and Indexes: When copying a table, constraints (like primary keys and unique indexes) and indexes are not automatically copied. You may need to recreate them manually.
• Storage Engine: The storage engine of the new table will be the same as the original table when using CREATE TABLE ... LIKE.
• Data Types: Ensure that the data types in the source and destination tables are compatible when copying data to an existing table.

Conclusion

Copying tables in MySQL can be done in several ways depending on your needs. This chapter covered how to copy only the structure, copy both structure and data, and copy data to an existing table.