Oracle SQL Query to Create Table

Oracle SQL Query to Create Table

Written by
Vinish Kapoor is a seasoned software development professional and a fervent enthusiast of artificial intelligence (AI). His impressive career spans over 20 years, marked by a relentless pursuit of innovation and excellence in the field of information technology. As an Oracle ACE, Vinish has distinguished himself as a leading expert in Oracle technologies, a title awarded to individuals who have demonstrated their deep commitment, leadership, and expertise in the Oracle community.
  • SQL
  • 2 mins read

Usually, we use an Oracle SQL query to create a table to create a copy of an existing table with or without the data. This is useful for making backups or tables with different names but the same structure.

Oracle SQL Query Examples

Below are some examples of Oracle SELECT statements to create the tables:

Example-1: Create a Table as a Copy of Another Table

The following SQL query will create a table EMP_2 as a copy of the EMP table with all the fields and all the data.

Create table EMP_2 
    as select * from EMP;

Example-2: Create a Table as a Copy of Existing Table Without the Data

To create a table as a copy of another table without the data use the WHERE clause with condition 1 = 2. Below is an example:

Create table EMP_2 
    as select * from EMP Where 1 = 2;

This will create the empty table EMP_2.

Example-3: Oracle SQL Query to Create a Table as a Copy of Another Table with Specific Fields

To create a table as a copy of another table with or without the data but with specific fields, then just specify the column names in your SQL query. Below is an example:

Create table EMP_2 
    as select empno, name, job, sal 
       from EMP;