Neoview SQL Reference Manual (R2.4 SP2)

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

0 2 (Value is generated by the internal sequence generator)

15 1

This may cause duplicates to be experienced because the current value of the internal sequence

generator is incremented to values that are already present in the IDENTITY column of table t2.

To avoid this problem, HP recommends that you determine a START WITH value that is greater

than the largest value of the IDENTITY column of the source table. For example:

SELECT MAX(a) FROM t1;

(EXPR)

---------

The CREATE TABLE AS statement for table t2 would then include internal sequence generator

options. This sets the current value of the created internal sequence generator table to a value

greater than the maximum value of the IDENTITY column to be added:

CREATE TABLE t2 (a LARGEINT GENERATED BY DEFAULT AS IDENTITY

(MINVALUE 10

MAXVALUE 99999

START WITH 16)

NOT NULL, b INT UNSIGNED NOT NULL,

PRIMARY KEY(a)) AS SELECT a,b FROM t1;

SELECT * FROM t2;

A B

15 1

INSERT INTO t2 VALUE(DEFAULT,1);

SELECT * FROM t2;

A B

CREATE TABLE Statement 85