Why to create a table from another table structure?

If we want 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 create a table with an existing table structure can happen when creating a backup table for the existing table.

How to create a table from another table structure?

We can create a new table as a copy of existing table by using CREATE TABLE AS Statement. New table gets same columns and structure of existing table.

We can create new table with all columns or specific columns of the existing table.

CREATE TABLE new_table_name AS
SELECT * 
FROM existing_table_name
WHERE condition; 

CREATE TABLE new_table_name AS
SELECT column1, column2,.., columnN
FROM existing_table_name
WHERE condition; 

• New_table_name - Specifies the new/target table name
• Existing_table_name - Specifies the source table name
• column1, column2, …, columnN - Specifies the column names
• Condition - Specifies condition to get the selected rows from the source.

There are various ways to create new table using existing table the queries are as follows -

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

Scenario - To get all columns from existing table to new table

We can create a new table which is exactly same as old table with all the columns and all the rows from it. The syntax for the same as follows -

CREATE TABLE new_table_name AS SELECT * FROM old_table_name; 

In the below example, we are creating an employee_details_backup table using employee_details table with all columns and all the data from it.

CREATE TABLE employee_details_backup AS SELECT * FROM employee_details;

After successful execution of the above query, employee_details_backup table gets created. It has all the columns and rows from the source table employee_details. The new table looks like below -

If we created a table like this, there will be no difference between them except table name.

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 -

CREATE TABLE new_table_name AS 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 AS 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 -

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 -

 CREATE TABLE new_table_name AS 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 AS 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 -

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 -

CREATE TABLE new_table_name AS 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 AS
  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 -