santosh-asp tutorial

CREATE TABLE AS Statement:-

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:-

  

        CREATE TABLE new_table  
    AS (SELECT * FROM old_table);

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".

  

     CREATE TABLE newcustomers  
    AS (SELECT *   FROM customers  WHERE customer_id < 5000);

This table is named as "newcustomers" and having the same columns of "customers" table.
Create Table Example: copying selected columns of another table:- Syntax:-

  

      CREATE TABLE new_table  
      AS (SELECT column_1, column2, ... column_n  
          FROM old_table);  

Let's take an example:

    CREATE TABLE newcustomers2  
    AS (SELECT customer_id, customer_name  
        FROM customers  
        WHERE customer_id < 5000);

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  "regularcustomers"   
       (    "RCUSTOMER_ID" NUMBER(10,0) NOT NULL ENABLE,   
        "RCUSTOMER_NAME" VARCHAR2(50) NOT NULL ENABLE,   
        "RC_CITY" VARCHAR2(50)  
       )  
    /

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

  

  CREATE TABLE  "irregularcustomers"   
       (    "IRCUSTOMER_ID" NUMBER(10,0) NOT NULL ENABLE,   
        "IRCUSTOMER_NAME" VARCHAR2(50) NOT NULL ENABLE,   
        "IRC_CITY" VARCHAR2(50)  
       )  
    /

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

  

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