Neoview SQL Reference Manual (R2.4 SP2)

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 LOAD IF EXISTS 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 append 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.
Considerations for CREATE TABLE AS
The CREATE TABLE AS statement is supported for the GENERATED BY DEFAULT AS
IDENTITY column. The values for the GENERATED BY DEFAULT AS IDENTITY can be
user-specified or system-generated. You cannot use the CREATE TABLE AS statement with the
GENERATED ALWAYS AS IDENTITY column.
In this GENERATED BY DEFAULT AS IDENTITY example, table t2 is created starting with 15
in the IDENTITY column, as defined in the CREATE TABLE statement.
CREATE TABLE t1 (a LARGEINT GENERATED ALWAYS AS IDENTITY
(MINVALUE 10
MAXVALUE 99999
START WITH 15)
NOT NULL,
b INT UNSIGNED NOT NULL,
PRIMARY KEY(a));
SELECT * FROM t1;
A B
15 1
INSERT INTO t1 VALUES(DEFAULT,1);
84 SQL Statements