SQL Copy Table

SQL COPY TABLE

The COPY TABLE operation is used to duplicate the structure and/or data of an existing table into a new table. Depending on requirements, you can copy only the structure, only the data, or both.

Syntax

To copy both structure and data:


CREATE TABLE new_table_name AS SELECT * FROM existing_table_name;

To copy only the structure:


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

Example

Let us copy the Staff table into a new table called Staff_Backup:

Code Example


-- Copy the Staff table into a new table
CREATE TABLE Staff_Backup AS SELECT * FROM Staff;

-- Verify the Copied Table
SHOW TABLES;

-- Display the Contents of the Copied Table
SELECT * FROM Staff_Backup;

Output

-- SHOW TABLES:

Tables_in_db_name
Staff
Staff_Backup

-- SELECT * FROM Staff_Backup:

ID	Name	Age	Department
2	Bob	25	IT
3	Charlie	35	Finance

Explanation

- The CREATE TABLE statement creates a new table Staff_Backup by copying the structure and data from Staff.
- The SHOW TABLES command confirms the creation of the new table.
- The SELECT * statement verifies that all data has been copied to the new table.

Best Practices

- Use meaningful names for backup tables for easy identification.
- Regularly back up critical tables to avoid accidental data loss.