Neoview SQL Reference Manual (R2.5)

ManualsBrandsHP ManualsSoftwareHP Neoview Release 2.5 Software

Create table t2 like table t1. 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;

SELECT MAX(a) from t2;

(EXPR)

--------

Table t2 is created with an internal sequence generator table START WITH value of 0. The

current value in the internal sequence generator is 0. To recalibrate the current value of the

internal sequence generator table, use the ALTER TABLE ALTER COLUMN command.

CREATE TABLE LIKE AS SELECT will fail with an error if the IDENTITY column from the target

table is a GENERATED ALWAYS AS IDENTITY column type. Only user-specified values are

valid for the AS SELECT feature of CREATE TABLE LIKE.

Recalibrating the Current Value of the Internal Sequence Generator Table

Recalibrating the current value of the internal sequence generator may be necessary after a

CREATE TABLE AS or CREATE TABLE LIKE AS command. The INSERT... SELECT from the

table creation will have provided user-specified values for the target table IDENTITY column.

The internal sequence generator associated with the target table IDENTITY column would remain

at its initial default value based on the START WITH property. This could lead to possible

duplicates occurring in subsequent row insertions.

To avoid inserts failing with duplicate errors, when a target table is created using the CREATE

TABLE LIKE ... AS SELECT statement, the internal sequence generator current value should be

recalibrated to a value greater than the maximum value currently in the IDENTITY column. This

allows subsequent inserts to have values generated that are unique and duplicates are avoided.

To recalibrate the current value of the internal sequence generator table, use the ALTER TABLE

ALTER COLUMN recalibrate-option.

Following is an example of table t2 that has been created using CREATE TABLE LIKE AS t1.

Table t1 has an IDENTITY column named 'a' with a START WITH property of 0. Table t2 has

been created like table t1. Column a is also an IDENTITY column defined with a START WITH

property of 0. This could lead to duplicate errors being generated until the current value of the

internal sequence generator table is larger than the maximum value of the IDENTITY column

in table t2. To alleviate the problem, the internal sequence generator table will be recalibrated

using the ALTER TABLE ALTER COLUMN RECALIBRATE command.

CREATE TABLE t1 (a LARGEINT

GENERATED BY DEFAULT AS IDENTITY

(MINVALUE 0

MAXVALUE 99999

START WITH 0)

NOT NULL,

b INT UNSIGNED NOT NULL,

PRIMARY KEY(a));

INSERT INTO t1 VALUES(default,1),(default,2),(default,3);

SELECT * FROM t1;

A B

-------- ---------

0 1

1 2

2 3

CREATE TABLE Statement 83