CREATE TABLE AS Statement

Education is not limited to just classrooms. It can be gained anytime, anywhere... - Ravi Ranjan (M.Tech-NIT)

 

The CREATE TABLE AS statement is used to create a table from an existing table by copying the columns of existing table.

Note: If you create the table in this way, the new table will contain records from the existing table.

Syntax:

  1. CREATE TABLE new_table  
  2. AS (SELECT * FROM old_table);   
  3. Create Table Example: copying all columns of another table

    In this example, we are creating a "newcustomers" table by copying all the columns from the already existing table "Customers".

  4. CREATE TABLE newcustomers  
  5. AS (SELECT *   FROM customers  WHERE customer_id < 5000);  
  6. Table created.
    

    This table is named as "newcustomers" and having the same columns of "customers" table.

    Create Table Example: copying selected columns of another table

    Syntax:
  7. CREATE TABLE new_table  
  8.   AS (SELECT column_1, column2, ... column_n  
  9.       FROM old_table);  
  10. Let's take an example:

  11. CREATE TABLE newcustomers2  
  12. AS (SELECT customer_id, customer_name  
  13.     FROM customers  
  14. The above example will create a new table called "newcustomers2". This table includes the specified columns customer_id and customer_name from the customers table.

    Create Table Example: copying selected columns from multiple tables

    Syntax:
  15. CREATE TABLE new_table  
  16. AS (SELECT column_1, column2, ... column_n  
  17.     FROM old_table_1, old_table_2, ... old_table_n);   
  18. Let's take an example: Consider that you have already created two tables "regularcustomers" and "irregularcustomers".

    The table "regularcustomers" has three columns rcustomer_id, rcustomer_name and rc_city.

  19.     WHERE customer_id < 5000);  

  1. CREATE TABLE  "regularcustomers"   
  2.    (    "RCUSTOMER_ID" NUMBER(10,0) NOT NULL ENABLE,   
  3.     "RCUSTOMER_NAME" VARCHAR2(50) NOT NULL ENABLE,   
  4.     "RC_CITY" VARCHAR2(50)  
  5.    )  
  6. /  

The second table "irregularcustomers" has also three columns ircustomer_id, ircustomer_name and irc_city.

  1. CREATE TABLE  "irregularcustomers"   
  2.    (    "IRCUSTOMER_ID" NUMBER(10,0) NOT NULL ENABLE,   
  3.     "IRCUSTOMER_NAME" VARCHAR2(50) NOT NULL ENABLE,   
  4.     "IRC_CITY" VARCHAR2(50)  
  5.    )  
  6. /  

In the following example, we will create a table name "newcustomers3" form copying columns from both tables.

Example:

  1. CREATE TABLE newcustomers3  
  2.   AS (SELECT regularcustomers.rcustomer_id, regularcustomers.rc_city, irregularcustomers.ircustomer_name  
  3.       FROM regularcustomers, irregularcustomers  
  4.       WHERE regularcustomers.rcustomer_id = irregularcustomers.ircustomer_id  
  5.       AND regularcustomers.rcustomer_id < 5000);