Neoview SQL Reference Manual (R2.5)

using the LIKE specification are defined as if the new table is created explicitly by the current
user.
For tables with IDENTITY columns, the target table inherits the IDENTITY property of a column
along with the corresponding sequence generating properties from the source table. Neither
Neoview SQL's CREATE TABLE LIKE support nor its behavior with respect to the IDENTITY
column is ANSI compliant.
CREATE TABLE LIKE Example
CREATE TABLE tbl1_src(
Id_col INTEGER UNSIGNED GENERATED ALWAYS AS IDENTITY
(
START WITH 1
INCREMENT BY 2) NOT NULL,
Col2 INTEGER NOT NULL, PRIMARY KEY(Id_col)
);
CREATE TABLE tbl_tgt LIKE tbl1_src;
These statements will result in tbl_tgt having the same column attributes and IDENTITY
column attributes as table tbl1_src.
CREATE TABLE tbl_tgt(
Id_col INTEGER UNSIGNED GENERATED ALWAYS AS IDENTITY
(
START WITH 1
INCREMENT BY 2) NOT NULL,
Col2 INTEGER NOT NULL, PRIMARY KEY(Id_col)
);
The CREATE TABLE LIKE statement using AS SELECT syntax inserts rows into the newly
created table. CREATE TABLE LIKE inherits the same internal sequence generator properties in
the target table as were defined in the source table. The AS SELECT clause selects rows from the
source table and provides user-specified values to the IDENTITY column for the target table. As
user-specified values are used, the internal sequence generator table created for the target table
will still contain a current value based on the IDENTITY column START WITH property.
Subsequent inserts, using the DEFAULT specification, might generate values using the internal
sequence generator table already present in the target table. This might lead to inserts failing
with duplicate errors. To avoid this problem, 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 Neoview SQL to generate unique values on subsequent inserts and to avoid duplicate
values.
Example 2 of CREATE TABLE LIKE
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
82 SQL Statements