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.

Our Random Articles

• Somdev Devvarman ousts Xavier Malisse 6-1, 3-6,7-6(5)
• How To Choose A Good Online Traffic School
• New Year Wishes 2010
• New Year Wishes
• How to use EXTRACT function with date and time columns

More Links