ReadVitamin

Creating and Populating New Table from Existing Table in ONE Step

Written by admin in Tuesday, July 17th 2007 under IT

A table can be created and populated from another existing table in a single step.The example below creates the table in perm space (space that is allocated to the database and not shared with other databases).

CREATE TABLE <username>.<table name> AS

(Select <column names>

FROM <table name>

WHERE condition)

with data;

The option “with data” will create the definitions for the new table using attributes from the existing table and populate the new table with data from the existing table. The option “with no data” will create the definition only and would not populate it.

If you want to create a new volatile (temporary) table from an existing volatile table, then code would be as follows;

CREATE SET VOLATILE TABLE <username>.<table name> AS

(Select <column names>

FROM <table name>

WHERE condition)

with data

PRIMARY INDEX ( “column name” )

ON COMMIT PRESERVE ROWS;

Make sure that you write the code “ON COMMIT PRESERVE ROWS” as this is the statement which would preserve the rows that is inserted on the fly. Try creating it without this line and see what happens.

Popularity: 9% [?]