MySQL Copy Table Statement

MySQL Copy Table Statement

The MySQL Copy Table process involves creating a new table with the same structure and data as an existing table. This is essential for creating backups, duplicating data for testing, or making structural changes without affecting the original table.

Syntax

Copy Table Structure Only

CREATE TABLE new_table LIKE existing_table;

Copy Table Structure and Data

CREATE TABLE new_table AS SELECT * FROM existing_table;

The CREATE TABLE statement has the following components:

  • new_table: The name of the new table to be created.
  • existing_table: The name of the existing table to be copied.

Example MySQL Copy Table Statements

Let's look at examples of the MySQL Copy Table process and how to use it:

Step 1: Using the Database

USE mydatabase;

This query sets the context to the database named mydatabase.

MySQL USE DATABASE

Step 2: Creating an Existing Table

Create an existing table to work with:

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

This query creates a table named employees with columns for id, first_name, last_name, and email.

MySQL CREATE TABLE

Step 3: Inserting Data into the Existing Table

Insert some data into the employees table:

INSERT INTO employees (first_name, last_name, email)
VALUES ('John', 'Doe', 'john.doe@example.com'),
       ('Jane', 'Smith', 'jane.smith@example.com');

This query inserts two rows into the employees table.

MySQL INSERT INTO TABLE

Step 4: Copying the Table Structure Only

Create a new table with the same structure as the employees table:

CREATE TABLE employees_copy LIKE employees;

This query creates a new table named employees_copy with the same structure as the employees table but without the data.

MySQL CREATE TABLE LIKE

Step 5: Copying the Table Structure and Data

Create a new table with the same structure and data as the employees table:

CREATE TABLE employees_backup AS SELECT * 
FROM employees;

This query creates a new table named employees_backup with the same structure and data as the employees table.

MySQL CREATE TABLE AS SELECT

Step 6: Verifying the Copied Tables

To verify that the tables have been copied, you can describe their structures and select data from them:

DESCRIBE employees_copy;
SELECT * 
FROM employees_backup;

These queries provide a detailed description of the employees_copy table structure and display the data from the employees_backup table.

MySQL DESCRIBE Copied TableMySQL SELECT Copied Table

Conclusion

The MySQL Copy Table process is a versatile tool for duplicating table structures and data. Understanding how to use the CREATE TABLE LIKE and CREATE TABLE AS SELECT statements is essential for effective database management and data manipulation in MySQL.