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.
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 col_1, col_2, ... FROM Source_Table_Name; Table_Name: The name of the backup table. AS: Aliasing col: Required columns from source table
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. 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.
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
Output :