Create a Table from another Table in Oracle SQL

In my previous article, you have seen how to create a Table and Global Temporary Table in Oracle. In this article, you will learn how to create a table from another table.

1) How to create a Table from an Existing Table?

2) How to create a Table from an Existing Table with filtered data?

3) How to create a Table from an Existing Table with few columns?

4) How to create a Table from two or more than two Tables?

5) How to create a Table structure from an Existing Table? (Without any data)

Note: When you create a new table from an existing table, the new table is populated with all the columns and data from an existing table.

1) Create a Table from an Existing Table:

Syntax: You can create a table from an existing table using below syntax:

CREATE TABLE new-table-name 
   AS (SELECT * FROM existing-table-name);

Example:

CREATE TABLE TB_EMP_DETAILS 
AS (SELECT * FROM TB_EMPLOYEE);

This example would create a new table called TB_EMP_DETAILS that would include all the columns and records from an existing table called TB_EMPLOYEE.

2) Create a Table from an Existing Table with filtered data:

Syntax: You can create a table from an existing table with filter data using below syntax:

CREATE TABLE new-table-name 
   AS (SELECT * FROM existing-table-name 
       WHERE column-name > values);

Example:

CREATE TABLE TB_EMP_DETAILS 
   AS (SELECT * FROM TB_EMPLOYEE
       WHERE EMP_SALARY>20000);

This example would create a new table called TB_EMP_DETAILS that would include all the columns and records of those employees whose salary is more than 20000 from an existing table called TB_EMPLOYEE.

3) Create a Table from an Existing Table with few/selected columns:

Syntax: You can create a Table with few/selected columns from an existing Table using below syntax.

CREATE TABLE new-table-name
  AS (SELECT column-1, column-2, ... column-n FROM old-table-name);

Example:

CREATE TABLE TB_EMP_DETAILS
  AS (SELECT EMP_ID, EMP_NAME, EMP_CITY FROM TB_EMPLOYEE);

This example would create a new table called TB_EMP_DETAILS that would include only selected columns and all the records from an existing table called TB_EMPLOYEE.

4) Create a Table from two or more than two Tables:

Syntax: You can create a Table from two or more than two tables from an existing Table using below syntax.

CREATE TABLE new-table-name
  AS (SELECT a.column-1, a.column-2, ... a.column-n,
             b.column-1,b.column-2, ... b.column-n 
             FROM old-table1 a, old-table2 b
             WHERE a.column-1=b.column-1);

Example:

CREATE TABLE TB_EMP_DETAILS
  AS (SELECT a.EMP_ID, a.EMP_NAME, a.EMP_CITY
             b.DEPT_NAME, b.DEPT_LOCATION
             FROM TB_EMPLOYEE a, TB_DEPT b
             WHERE a.EMP_ID=b.DEPT_ID);

This example would create a new table called TB_EMP_DETAILS that would include columns and all the records from the existing tables in FROM clause called TB_EMPLOYEE, TB_DEPT.

5) Create a Table structure from an Existing Table:

Syntax: You can create a table structure from an existing table using below syntax:

CREATE TABLE new-table-name
  AS (SELECT * FROM old-table-name WHERE 1=2);

Example:

CREATE TABLE TB_EMP_DETAILS
  AS (SELECT * FROM TB_EMPLOYEE WHERE 1=2);

This example would create a new table called TB_EMP_DETAILS that would include only columns without any data from an existing table called TB_EMPLOYEE.