SQL COPY TABLE

SQL COPY TABLE Operation

The SQL COPY TABLE operation is used to create a new table by copying the structure and optionally the data from an existing table. This can be useful for creating backups, duplicating table designs, or testing purposes.

---

Syntax

To copy only the structure of the table:

CREATE TABLE new_table_name AS
SELECT * FROM old_table_name WHERE 1=0;

To copy both the structure and data:

CREATE TABLE new_table_name AS
SELECT * FROM old_table_name;

• CREATE TABLE: This is the SQL keyword used to create a new table.
• new_table_name: This specifies the name of the new table you want to create.
• AS SELECT *: This specifies that the new table should be created based on the existing table.
• old_table_name: This specifies the name of the existing table from which you want to copy.
• WHERE 1=0: This ensures that no data is copied, only the table structure.

---

Example

Let's go through a complete example that includes creating a database, creating a table, inserting data into the table, and then copying the table.

Step 1: Creating a Database

This step involves creating a new database where the table will be stored.

CREATE DATABASE example_db;

In this example, we create a database named example_db.

Step 2: Creating a Table

In this step, we create a table named employees within the previously created database.

USE example_db;

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

Here, we define the employees table with columns for id, first_name, last_name, email, and hire_date. The id column is set as the primary key and will auto-increment.

Step 3: Inserting Data into the Table

This step involves inserting some sample data into the employees table.

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

Here, we insert two rows of data into the employees table.

Step 4: Copying the Table Structure Only

This step involves copying the structure of the employees table to a new table named employees_structure_copy without copying any data.

CREATE TABLE employees_structure_copy AS
SELECT * FROM employees WHERE 1=0;

This command will create a new table employees_structure_copy with the same structure as the employees table but without any data.

Step 5: Copying the Table Structure and Data

This step involves copying both the structure and data of the employees table to a new table named employees_full_copy.

CREATE TABLE employees_full_copy AS
SELECT * FROM employees;

This command will create a new table employees_full_copy with the same structure and data as the employees table.