SQL Query to Copy, Duplicate or Backup Table

In relational databases, we often deal with different tables and perform various operations using different database software like MYSQL, Oracle, PostgreSQL, etc.

Sometimes, while performing operations on a table, we might need to create a table backup. We can create a backup of the table by creating its copy or duplicate. This allows users to maintain the integrity and security of the original database.

We can track changes in data using the backup table when we perform various modification operations. So, in this article, we will learn how to copy, duplicate, or backup tables in SQL. Creating a table copy or duplicate is the same as creating the backup.

We will be using the following table “Student Information” which consists of data of Beginner who enrolled in our DSA course as shown below:

We can create a backup of a table by creating a duplicate or copy of original database.

The syntax to create a copy/duplicate/backup of a table in SQL is:

CREATE TABLE Table_Name AS SELECT * FROM Source_Table_Name;

Here,

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

Let’s look at some examples on how to copy/duplicate table in SQL to create a backup table:

SQL Backup Table with All Columns Data Example

In this example, we will create a backup table”stud_1″ of “student_information” table by creating a copy of “student_information” table and copying its all columns with data.

Query:

CREATE TABLE stud_1 AS SELECT * FORM student_information;
SELECT * FROM stud_1;

Output :

SQL Backup Table with Specific Column Data Example

In this example, we will create a backup table”stud_2″ of “student_information” table by creating a copy of “student_information” table and only copying specific columns with data.

CREATE TABLE stud_2 AS
SELECT id,student_name FROM student_information;
SELECT * FROM stud_2;

Output :

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.

SQL Backup Table with No Data Example

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.

In this example, we will create a backup table “Beginner_student” of “student_information” table by creating a copy of “student_information” table and copying its all columns without data.

Query:

CREATE TABLE Beginner_student AS SELECT * FROM student_information
WHERE 1!=1;
SELECT * FROM Beginner_student;

Output :

SQL Backup Table with Specific Columns and No Data Example

In this example, we will create a backup table “geek_student” of “student_information” table by creating a copy of “student_information” table and copying specific columns without data.

Output :