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);

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

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 Example: copying selected columns from multiple tables

  CREATE TABLE new_table
  AS (SELECT column_1, column2, ... column_n
      FROM old_table_1, old_table_2, ... old_table_n); 

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.

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);