Why to copy table?

If we require to use the same table structure for different table, we can use the copy of the existing table. Most of the cases, the requirement to copy a table from the existing table can happen when creating a backup table for the existing table.

And also in real time, every one doesn't require to access the same data and if planning to use the security levels of data and instead of giving all the data access to everyone, we can copy tables to give only selected data to them.

SELECT with INTO clause or CREATE with SELECT statement used to fulfil the requirement.

How to copy table data?

SELECT with INTO clause or CREATE with SELECT statement used to copy table data into another table by using various ways of querying.

CREATE TABLE new_table 
SELECT column1, column2, …, columnN FROM old_table
 WHERE <condition(s)>; 

• new_table - Specifies the name of new table.
• column1, column2, …, columnN - Specifies the column names that are going to be part of new table.
• old_table - Specifies the name of the existing table with the data.

SELECT column1, column2, …, columnN INTO new_table FROM old_table
 WHERE <condition(s)>; 

• new_table - Specifies the name of new table.
• column1, column2, …, columnN - Specifies the column names that are going to be part of new table.
• old_table - Specifies the name of the existing table with the data.

Let us consider below table(s) as an example table(s) to frame the SQL query for getting the desired results.

Scenario – To copy all the rows from another table

To copy the entire data from an existing table into a new table in same database, we can use CREATE with SELECT command.

CREATE new_table
SELECT * FROM old_table_name ; 

In the below example, we are creating employee_details_backup from employee_details table with all the rows. Query for the same is –

CREATE employee_details_backup
SELECT * FROM employee_details;  

Once the above statement executed successfully, then we can conclude that the table gets created successfully. However, it is always best practice to verify once before confirming to the external world.

To verify the table gets created or not, just trigger a select query on the table like below -

SELECT * FROM employee_details_backup; 

Table should gets displayed like below -

Scenario - To copy table from different Database

To copy the entire data from an existing table of the different database into a new table in current database, we can use CREATE with SELECT command.

CREATE new_table 
SELECT * FROM new_database.old_table_name; 

In the below example, we are creating employee_details_backup in the current database from employee_details table that is existing on empdb. Query for the same is -

CREATE employee_details_backup
SELECT * FROM empdb.employee_details;   

Once the above statement executed successfully, then we can conclude that the table gets created successfully.

To verify the table gets created or not, just trigger a select query on the table like below –

SELECT * FROM employee_details_backup; 

Table should gets displayed like below if the query execution is successful -

Scenario - To get specified records from existing table

We can create a new table which is exactly same as old table with all the columns. We can also specify the condition to copy matched rows only. The syntax for the same as follows -

CREATE TABLE new_table_name SELECT * FROM old_table_name 
WHERE condition; 

In the below example, we are creating an employee_details_backup table using employee_details table with all columns and only rows that are having department 1000.

CREATE TABLE employee_details_backup SELECT * FROM employee_details
WHERE dept_id = 1000; 

After successful execution of the above query, employee_details_backup table gets created. It has all the columns but only with department 1000 rows from the source table employee_details. The new table looks like below -

Scenario - To get the specific columns from existing table

We can create a new table which is exactly same as old table with the specified columns (not all). The syntax for the same as follows -

 CREATE TABLE new_table_name SELECT column1, column2, …, columnN
 FROM old_table_name; 

In the below example, we are creating an employee_details_backup table using employee_details table with emp_id, emp_name, designation, and salary columns and all the rows from it.

CREATE TABLE employee_details_backup SELECT emp_id, emp_name,
 designation, salary FROM employee_details; 

After successful execution of the above query, employee_details_backup table gets created. It has only emp_id, emp_name, designation, salary columns but have the data for the specific rows from the source table employee_details. The new table looks like below -

Scenario - To get only structure of the existing table

We can create a new table which is exactly same as old table with all columns but no data from the source table. i.e. we are copying only structure without data. The syntax for the same as follows -

CREATE TABLE new_table_name SELECT * FROM old_table_name WHERE 1 = 2; 

In the below example, we are creating an employee_details_backup table using employee_details table with all columns but not the data from it. i.e. we are copying only structure without data.

CREATE TABLE employee_details_backup
 SELECT * FROM employee_details WHERE 1 = 2; 

After successful execution of the above query, employee_details_backup table gets created. It has all the columns from source table but not the data. The new table looks like below -