?️ MySQL Copy Table

? Quick Overview

Copying tables in MySQL can mean duplicating structure, data, or both. Common approaches: CREATE TABLE ... AS SELECT to create-and-copy, and INSERT INTO ... SELECT to copy into an existing table.

? Key Concepts

Structure vs Data: You can copy only columns (structure) or include rows (data).
Indexes & Constraints: Not auto-copied — re-create them manually if needed.
Selective Copy: Use WHERE and column lists to copy a subset of rows/columns.
Existing Target: Use INSERT INTO ... SELECT when the target table already exists.

? Syntax / Theory

? View Code Example

// Copy structure + data into new_table
CREATE TABLE new_table AS
SELECT * FROM old_table;

// Copy only structure (no rows)
CREATE TABLE new_table AS
SELECT * FROM old_table WHERE 1=0;

// Copy data into an existing table
INSERT INTO new_table
SELECT * FROM old_table;

// Copy specific columns into a new table
CREATE TABLE new_table AS
SELECT id, name FROM old_table;

? Code Example(s)

? View Code Example

// Create original Employees table and insert sample rows
CREATE TABLE Employees (
ID INT PRIMARY KEY,
Name VARCHAR(100),
Salary DECIMAL(10,2)
);

// Insert sample rows
INSERT INTO Employees VALUES (1,'Alice',5000.00),(2,'Bob',6000.00);

// Copy structure + data into a new backup table
CREATE TABLE Employees_Backup AS
SELECT * FROM Employees;

// Copy structure only into a new table (no data)
CREATE TABLE Employees_Structure AS
SELECT * FROM Employees WHERE 1=0;

// Copy only rows matching condition
CREATE TABLE HighSalaryEmployees AS
SELECT * FROM Employees WHERE Salary > 5500;

// Insert rows into existing table (append)
INSERT INTO Employees_Backup
SELECT * FROM Employees;

? Live Output / Explanation

Explanation

CREATE TABLE ... AS SELECT: Builds a new table from query results (structure + data).
Structure only: Using WHERE 1=0 returns no rows, copying only columns.
INSERT INTO ... SELECT: Appends data into an already-existing table with a matching schema.
Indexes/Constraints: Indexes, primary keys, foreign keys, and auto-increment are not copied; run SHOW CREATE TABLE to replicate them manually.

? Use Cases

Quick backups before running risky migrations.
Generating reporting or analytics tables from a subset of rows.
Cloning table structure for test data or staging environments.

? Tips & Best Practices

Add indexes, primary keys, or constraints manually after copying when needed.
Run SHOW CREATE TABLE table_name; to obtain full DDL including indexes.
For large tables, copy in chunks or use WHERE filters to reduce load and space usage.
Verify column order and types when inserting into existing tables to avoid mismatch errors.

? Try It Yourself / Practice Tasks

Create a backup table from an existing table using CREATE TABLE ... AS SELECT.
Copy only employees with salary > 5500 into a new table and verify row counts.
Use SHOW CREATE TABLE on the original table, recreate indexes on the backup, then compare performance.