Creating Tables using Subqueries

Creating Tables with help of Subqueries:

We can use Subqueries to create tables from existing tables.

We would use this technique if

• If we want to select only few columns or add derived columns to the new table.

• If we want to select only subset of rows.

Example of creating empty table using subquery:

CREATE TABLE newtable as (Select column1,column2,column3 from oldtable) WITH NO DATA;

Example of creating populated table using subquery:

CREATE TABLE newtable as (Select column1,column2,column3 from oldtable) WITH DATA;

However creating table using subquery has few drawback as follows:
 

1. Table level attributes are not copied. Ex: FALLBACK is not copied, the system default of NO FALLBACK is used.
    

2. Secondary indexes if present are not copied.
    

3. NOT NULL attribute on columns is not copied.
    
4. COMPRESS on columns is lost
    
5. DEFAULTs on columns is not copied.
    
6. PRIMARY index is not copied. The 1st column becomes the non unique primary index(NUPI)

We can rename the columns in the new table. Following are the ways of renaming the tables:

1. Syntax 1

CREATE TABLE newtable AS

(SELECT

 COLUMN1 as COL1,

 COLUMN2 as COL2,

 COLUMN3 as COL3

FROM oldtable

) WITH NO DATA ;

Newtable will have column names as COL1,COL2 and COL3 instead od COLUMN1,COLUMN2 and COLUMN3

1. Syntax 2

CREATE TABLE newtable (COL1,COL2,COL3) AS

(SELECT

 COLUMN1,

 COLUMN2,

 COLUMN3

FROM oldtable

) WITH NO DATA;

We can also change column attributes- column names, like datatype, NOT NULL constraints, CHECK constraints etc.

However care need to be taken that change to the datatype needs to happen in the select query. We can use implicit or explicit cast.

Change to any other attribute can happen in both the select query and the parameter list.

Example:

CREATE TABLE newtable (COL1 CHECK (col1 > 1000) ,COL2 default 0,COL3) AS

(SELECT

 COLUMN1 (INTEGER),   --> changing the data type should happen in the SELECT part

 CAST (COLUMN2 AS INTEGER) ,

 COLUMN3

FROM oldtable

) WITH NO DATA;

The subquery used to create the table can be a join of multiple tables also.

We can make columns in new table as UNIQUE or PRIMARY KEY. As we know that for columns to be UNIQUE or PRIMARY KEY they should be specified as NOT NULL.

WITH/ WITH NO DATA for Volatile and Global volatile tables

Volatile tables can also be created using existing tables WITH DATA and WITH NO DATA.

However we should note that by default the option of volatile tables is 'ON COMMIT DELETE ROWS'. So even if you create a table WITH DATA there might be no data as the 'ON COMMIT DELETE ROWS' will delete all the data.

So when we create volatile tables using WITH DATA we should make sure we write ON COMMIT PRESERVE ROWS.

Global Temporary tables cannot be created using the WITH DATA option.