Neoview SQL Reference Manual (R2.5)

The NO LOAD option in a CREATE TABLE AS statement creates a table with the CREATE
TABLE AS statement, but does not load data into the table. The option is useful if you need to
create a table to review its structure and to analyze the SELECT part of the CREATE TABLE AS
statement with the EXPLAIN statement. You can also use EXPLAIN to analyze the implicated
INSERT/SELECT part of the CREATE TABLE AS ... NO LOAD statement. For example:
CREATE TABLE ttgt NO LOAD AS (SELECT ...);
CREATE TABLE AS ... GENERATED BY DEFAULT AS IDENTITY
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));
INSERT INTO t1 VALUES(DEFAULT,1);
SELECT * FROM t1;
A B
15 1
CREATE TABLE t2 (a LARGEINT GENERATED BY DEFAULT AS IDENTITY NOT NULL,
b INT, PRIMARY KEY(a)) AS SELECT a,b FROM t1; --A full select
--list is required
SELECT * FROM t2;
A B
15 1
The CREATE TABLE AS SELECT statement, using a full select list from the source table, provides
the values for the GENERATED ALWAYS AS IDENTITY column as user-specified values. The
target table will contain the same values for the IDENTITY column as were selected from the
source table. As user-specified values are provided for the IDENTITY column, the internal
sequence generator associated with table t2 has not been updated with these values. A subsequent
insert into table t2 using the internal sequence generator will generate values as specified by
the internal sequence generator options.
In this example, the START WITH option is not defined, so the internal sequence generator starts
with a default value of '0'. When an insert is performed, the internal sequence generator will
generate values to be used as the IDENTITY column value.
INSERT INTO t2 VALUES(DEFAULT,2);
SELECT * FROM t2;
A B
15 1
0 2 (Value is generated by the internal sequence generator)
CREATE TABLE Statement 85