CREATE TABLE AS in Oracle

CREATE TABLE AS To create a table from an existent table, the CREATE TABLE AS statement is used. It copies the columns of the existing table to the new table.

Syntax: For creating a table by copying all columns of another table.

CREATE TABLE current_table  
AS (SELECT * FROM existing_table);  

Syntax: For creating a table by copying selected columns of another table.

CREATE TABLE new_table  
  AS (SELECT column_1, column_2, ... column_n  
      FROM existing_table);  

Syntax: For creating a table by copying selected columns from multiple tables.

CREATE TABLE new_table  
AS (SELECT column_1, column_2, ... column_n  
    FROM existing_table1, existing_table2, ... existing_tablen);   

Example1: Creating a table by copying all columns of another table.

CREATE TABLE new_students  
AS (SELECT *  FROM students WHERE student_id < 50);

Explanation: Here, “students” is an already existing table, and a new table will be created, named as the “new_students” and having the columns which are the same as the “students” table.

Example 2: Creating a table by copying selected columns of another table.

CREATE TABLE new_students  
AS (SELECT student_id, student_name  
    FROM students WHERE student_id < 50);

Explanation: Here, “students” is an already existing table, and a new table will be created, named as the “new_students” and specified columns student_id and student_name from the “students” table are copied to the new table.

Example 3: Creating a table by copying selected columns from multiple tables. Existing table 1:

CREATE TABLE  "hindi_students"   
   (    "HSTUDENTS_ID" NUMBER(10,0) NOT NULL ENABLE,   
    "HSTUDENTS_NAME" VARCHAR2(40) NOT NULL ENABLE,   
    "HSTUDENTS_CLASS" VARCHAR2(10)  
   )

Existing table 2:

CREATE TABLE  "english_students"   
   (    "ESTUDENTS_ID" NUMBER(10,0) NOT NULL ENABLE,   
    "ESTUDENTS_NAME" VARCHAR2(40) NOT NULL ENABLE,   
    "ESTUDENTS_CLASS" VARCHAR2(10)  
   )

New Table:

CREATE TABLE new_students  
  AS (SELECT hindi_students.HSTUDENTS_ID, hindi_students.HSTUDENTS_CLASS, 
      english_students.ESTUDENTS_NAME  
      FROM hindi_students, english_students  
      WHERE hindi_students.HSTUDENTS_ID = english_students.ESTUDENTS_ID
      AND hindi_students.HSTUDENTS_ID < 50);

Explanation: Here, “hindi_students” and “english_students” are both already existing table, and a new table will be created, named as the “new_students” and specified columns HSTUDENTS_ID and HSTUDENTS_CLASS from the “hindi_students” table along with the specified column ESTUDENTS_CLASS are copied to the new table.

Please follow and like us: