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

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:

Let’s take an example:

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:

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.

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

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

Example: