SQL COPY TABLE

Introduction

In this chapter, we will learn how to copy a table in SQL. Copying a table can be useful for creating backups, testing changes, or transferring data between tables. This chapter will cover the syntax and methods to copy a table, including copying the structure only and copying both the structure and data.

Methods to Copy a Table

There are multiple ways to copy a table in SQL. The most common methods are:

1. Copying the table structure only
2. Copying the table structure and data

Copying the Table Structure Only

To copy only the structure of a table (i.e., creating a new table with the same columns but without any data), you can use the CREATE TABLE ... AS statement with a WHERE clause that is always false, such as WHERE 1 = 0.

Syntax

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

Example

Let's assume we have a table named employees and we want to copy its structure to a new table named employees_backup.

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

This command creates a new table named employees_backup with the same columns as employees but without any data.

Copying the Table Structure and Data

To copy both the structure and the data of a table, you can use the CREATE TABLE ... AS statement without the WHERE clause.

Syntax

CREATE TABLE new_table_name AS SELECT * FROM old_table_name;

Example

Using the same employees table, we will copy its structure and data to a new table named employees_full_backup.

CREATE TABLE employees_full_backup AS SELECT * FROM employees;

This command creates a new table named employees_full_backup with the same columns and data as employees.

Step-by-Step Example

1. Create a Sample Table

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

2. Insert Sample Data

INSERT INTO employees (first_name, last_name, email)
VALUES ('Ramesh', 'Kumar', 'ramesh.kumar@example.com'),
       ('Sita', 'Patel', 'sita.patel@example.com'),
       ('Arjun', 'Singh', 'arjun.singh@example.com');

3. Copy Table Structure Only

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

4. Verify the Table Structure

DESCRIBE employees_backup;

Output

Field	Type	Null	Key	Default	Extra
id	int(11)	NO	PRI	NULL	auto_increment
first_name	varchar(50)	YES		NULL
last_name	varchar(50)	YES		NULL
email	varchar(100)	YES	UNI	NULL

5. Copy Table Structure and Data

CREATE TABLE employees_full_backup AS SELECT * FROM employees;

6. Verify the Table Data

SELECT * FROM employees_full_backup;

Output

id	first_name	last_name	email
1	Ramesh	Kumar	ramesh.kumar@example.com
2	Sita	Patel	sita.patel@example.com
3	Arjun	Singh	arjun.singh@example.com

Using INSERT INTO ... SELECT

Another method to copy data from one table to another is by using the INSERT INTO ... SELECT statement. This method is useful if you want to copy data into an existing table.

Syntax

INSERT INTO new_table_name (column1, column2, ...)
SELECT column1, column2, ...
FROM old_table_name;

Example

Assume we have an existing table named employees_new with the same structure as employees:

INSERT INTO employees_new (id, first_name, last_name, email)
SELECT id, first_name, last_name, email
FROM employees;

Conclusion

Copying tables in SQL is a common task that can be done using various methods. This chapter covered how to copy the structure of a table, copy both the structure and data, and use the INSERT INTO ... SELECT statement to copy data to an existing table. Understanding these techniques is essential for effective database management.