SQL Query to Copy, Duplicate or Backup Table

In relational databases, we often deal with different tables and perform various operations using this different database software like MYSQL, Oracle, PostgreSQL, etc. Sometimes, while performing these operations many of us want to keep a backup table which is beneficial and can be used as a reference or can be reused if needed. Similarly, many times we need to copy the same table again and create a duplicate version of itself. 

We can track changes of data using the backup table when we perform various modification operations. So, in this article, we are going to discuss how to copy as well as create a backup table in SQL.

Sample Input: Consider a schema “Student Information” which consists of data of Geeks who enrolled in our DSA course as shown below:

Student Information
ID	Age	Student Name	Sex
1	22	Harry	Male
2	23	Vishal	Male
3	20	Snehal	Female
4	25	Ram	Male
5	24	Hina	Female

Syntax:
CREATE TABLE Table_Name AS SELECT * FROM Source_Table_Name;

Table_Name: The name of the backup table.
AS: Aliasing

In MYSQL, we can use the following command to check the number of tables created in the database before and after a backup. However, this command is not supported in PostgreSQL and in other versions of SQL.

SHOW TABLES;

Example 1: We can copy all the columns in the backup table.

Backup Table 1 Query

Output :

Output of Backup Table 1

Example 2: It is not mandatory to copy all the columns. We can take a few columns as well.

Syntax:
CREATE TABLE Table_Name AS SELECT col_1, col_2, ... FROM Source_Table_Name;

Table_Name: The name of the backup table.
AS: Aliasing
col: Required columns from source table

Backup Table 2 Query

Output :

Output of Backup Table 2

Till now we have seen how to create a  clone of the source table. In the above backup table, the data is also copied along with the table. However, we can also create a backup table without copying the data. So, to create a table without any data being copied we can use the help of the WHERE clause which needs to return a FALSE value. For example, we can use WHERE 2<2 or WHERE 1=2.

Syntax:
CREATE TABLE Table_Name AS SELECT * FROM Source_Table_Name
WHERE (RETURN FALSE);

Table_Name: The name of the backup table.
AS: Aliasing
FALSE: Any expression which returns FALSE. For example 4>5

Example 1: All the columns copied without any data.

Query For Backup Table

Output :

Backup Table Output

Example 2: It is not mandatory to copy all the columns. We can take a few columns as well.

Syntax:
CREATE TABLE Table_Name AS SELECT col1,col2,.... Source_Table_Name
WHERE (RETURN FALSE);

Table_Name: The name of the backup table.
AS: Aliasing
col: Required columns from source table
FALSE: Any expression which returns FALSE. For example 4>5

Query For Backup Table

Output :

Backup Table Output