Neoview SQL Reference Manual (R2.5)

The CREATE TABLE LIKE target table t2 will inherit the internal sequence generator properties
from the source table t1. The AS SELECT syntax will select all rows from table t1 and insert
them into table t2. The IDENTITY column values will be user-specified values.
CREATE TABLE t2 LIKE t1 AS SELECT a,b FROM t1;
Table t2 has been created with an internal sequence generator table START WITH value of 0.
The current value in the internal sequence generator table is 0.
ALTER TABLE t2 ALTER COLUMN a RECALIBRATE;
The current maximum value of the IDENTITY column is added to the INCREMENT BY property
of the internal sequence generator table and saved as the new current value in the internal
sequence generator table. The recalibration of the internal sequence generator current value is
now complete. Unique values will be generated for the IDENTITY column. For details on the
syntax of ALTER TABLE and the recalibrate options, see the “ALTER TABLE Statement”
(page 44).
NOTE: If the table containing the IDENTITY column was newly created and has no rows added,
the ALTER TABLE ALTER COLUMN RECALIBRATE operation is reported as successful.
However, the internal sequence generator current value remains unchanged as no update is
necessary.
Considerations for CREATE TABLE AS
These considerations apply to CREATE TABLE AS:
• Access to the table built by CREATE TABLE AS will be a full table scan because a primary
and clustering key cannot be easily defined.
• Compile time estimates and runtime information is not generated for CREATE TABLE AS
tables.
• You cannot manage CREATE TABLE AS tables using WMS compile time or runtime rules.
• You cannot specify a primary key for a CREATE TABLE AS table without explicitly defining
all the columns in the CREATE TABLE statement.
• You cannot generate an explain plan for a CREATE TABLE AS ...INSERT/SELECT statement.
You can, however, use the EXPLAIN plan for a CREATE TABLE AS ... INSERT/SELECT
statement if you use the NO LOAD option.
• You cannot use the ORDER BY clause in a CREATE TABLE AS statement. The compiler
transparently orders the selected rows to improve the efficiency of the insert.
• Information in the section “CREATE TABLE AS ... GENERATED BY DEFAULT AS
IDENTITY” (page 85).
Considerations for LOAD IF EXISTS and NO LOAD options of CREATE TABLE AS
The LOAD IF EXISTS option in a CREATE TABLE AS statement statement causes data to be
loaded into an existing table. If you do not specify the LOAD IF EXISTS option and try to load
data into an existing table, the CREATE TABLE AS statement fails to execute. Use the LOAD IF
EXISTS option with the AS clause in these scenarios:
• Running CREATE TABLE AS without re-creating the table. The table must be empty.
Otherwise, the CREATE TABLE AS statement returns an error. Delete the data in the table
by using PURGEDATA or a DELETE statement before issuing the CREATE TABLE AS
statement.
• Using CREATE TABLE AS to incrementally add data to an existing table. You must start a
user-defined transaction before issuing the CREATE TABLE AS statement. If you try to
execute the CREATE TABLE AS statement without starting a user-defined transaction, an
error is returned, stating that data already exists in the table. With a user-defined transaction,
newly added rows are rolled back if an error occurs.
84 SQL Statements