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.

MySQL, PostgreSQL Syntax –

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.

SQL Server Syntax -

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.

Example -

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

employee_details -

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 60000.00 1000
002 Employee2 Director 2019-07-11 40000.00 2000
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000
004 Employee4 Manager Employee2 2019-10-08 25000.00 2000
005 Employee5 Analyst Employee3 2019-07-11 20000.00 1000
006 Employee6 Analyst Employee3 2019-10-08 18000.00 1000
007 Employee7 Clerk Employee3 2019-07-11 15000.00 1000
008 Employee8 Salesman Employee4 2019-09-09 14000.00 2000
009 Employee9 Salesman Employee4 2019-10-08 13000.00 2000

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;  

Verifying table -

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 -

employee_details_backup -

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 60000.00 1000
002 Employee2 Director 2019-07-11 40000.00 2000
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000
004 Employee4 Manager Employee2 2019-10-08 25000.00 2000
005 Employee5 Analyst Employee3 2019-07-11 20000.00 1000
006 Employee6 Analyst Employee3 2019-10-08 18000.00 1000
007 Employee7 Clerk Employee3 2019-07-11 15000.00 1000
008 Employee8 Salesman Employee4 2019--09-09 14000.00 2000
009 Employee9 Salesman Employee4 2019-10-08 13000.00 2000

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 -

employee_details_backup -

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 60000.00 1000
002 Employee2 Director 2019-07-11 40000.00 2000
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000
004 Employee4 Manager Employee2 2019-10-08 25000.00 2000
005 Employee5 Analyst Employee3 2019-07-11 20000.00 1000
006 Employee6 Analyst Employee3 2019-10-08 18000.00 1000
007 Employee7 Clerk Employee3 2019-07-11 15000.00 1000
008 Employee8 Salesman Employee4 2019--09-09 14000.00 2000
009 Employee9 Salesman Employee4 2019-10-08 13000.00 2000

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 -

Syntax -

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 -

employee_details_backup -

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 60000.00 1000
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000
005 Employee5 Analyst Employee3 2019-07-11 20000.00 1000
006 Employee6 Analyst Employee3 2019-10-08 18000.00 1000
007 Employee7 Clerk Employee3 2019-07-11 15000.00 1000

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 -

Syntax -

 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 -

employee_details_backup -

emp_id emp_name designation salary
001 Employee1 Director 60000.00
002 Employee2 Director 40000.00
003 Employee3 Manager 27000.00
004 Employee4 Manager 25000.00
005 Employee5 Analyst 20000.00
006 Employee6 Analyst 18000.00
007 Employee7 Clerk 15000.00
008 Employee8 Salesman 14000.00
009 Employee9 Salesman 13000.00

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 -

Syntax -

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 -

employee_details_backup -

emp_id emp_name designation manager_id date_of_hire salary dept_id