Open In App

SQL Query to Copy, Duplicate or Backup Table

Improve
Improve
Like Article
Like
Save
Share
Report

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


Last Updated : 15 May, 2021
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads