Create Table As Select Oracle

CREATE TABLE AS statement is used to create a new table that exactly appears as another data in the existing table in the database.

Oracle Create Table As Syntax

CREATE TABLE New_Table_Name as
(Select Column from Old_Table_Name);

Oracle Create Table As Example

Suppose we have an existing table Student. You want to make some changes to the Values present in Student Table. However, on the other hand, you don't want the original Value in the Student Table to be altered.

What can be done in such cases? 

One solution is, we can just create a duplicate table that consists of the same values as in the Student Table and can apply changes in that. Creating another new table and again inserting all the values in the table can be very time-consuming. Therefore, this can be done easily by using CREATE TABLE AS statement in SQL.

Student Table is given below:

Now, to create Duplicate table named Dupli by using CREATE TABLE AS:

CREATE TABLE AS DUPLI
( Select * from Student);

Output

NOTE: 

1. The table created using CREATE TABLE AS Statement will have exactly same column as that of the existing table from which it is originated.
2. The table will have same values as that of an existing table.

Oracle Create Table As Columns

Let's consider different case, where we want to just make changes in Roll_No Column of an existing Stu table without changing it's original values. In such case we can just create a table containing only Roll_no column as:

Create Table Dupli_Rollno as
(Select Roll_no from Student);

Output

Also, after implementing Select statement we will have Dupli_Rollno table as: