CREATING A TABLE FROM A TABLE

Syntax:

CREATE TABLE (, )

AS SELECT , FROM ;

Example :

Create a table named ACCT_DTLS having three fields i.e. ACCT_NO, BRANCH_NO and CURBAL from the source table named ACCT_MSTR and rename the field CURBAL to BALANCE.

CREATE TABLE ACCT_DTLS (ACCT_NO, BRANCH_NO, BALANCE)

AS SELECT ACCT_NO, BRANCH_NO, CURBAL FROM ACCT_MSTR;

Output:

Table created.

The Source table is the table identified in the SELECT section of this SQL sentence. The Target table is one identified in the CREATE section. This SQL sentence populates the Target table with data from the Source table.

To create a Target table without the records from the source table (i.e. create the structure only), the SELECT statement must have a WHERE clause. The WHERE clause must specify a condition that cannot be satisfied.

This means the SELECT statement in the CREATE TABLE definition will not retrieve any rows from source table, it will just retrieve the table structure thus the target table will be created empty.

Example:

Create a table named ACCT_DTLS having three fields i.e. ACCT_NO, BRANCH_NO and CURBAL from source table named ACCT_MSTR and rename the field CURBAL to BALANCE. T'he table ADDR_DTLS should not be populated with any records.

CREATE TABLE ACCT_DTLS (ACCT_NO, BRANCH_NO, BALANCE)

AS SELECT ACCT_NO, BRANCH_NO, CURBAL FROM ACCT_MSTR WHERE 1=2;

Output:

Table created.