Neoview SQL Reference Manual (R2.4 SP2)

LIKE source-table [include-option]...
directs Neoview SQL to create a table like the existing table, source-table, omitting
constraints (with the exception of the NOT NULL and PRIMARY KEY constraints), and
partitions unless include-option clauses are specified.
source-table
is the ANSI logical name for the existing table and must be unique among names of tables,
views, and procedures within its schema.
include-option
WITH CONSTRAINTS
directs Neoview SQL to use constraints from source-table. Constraint names for
table are randomly generated unique names.
When you perform a CREATE TABLE LIKE, whether or not you include the WITH
CONSTRAINTS clause, the target table will have all the NOT NULL column constraints
that exist for the source table with different constraint names.
WITH PARTITIONS
directs Neoview SQL to use partition definitions from source-table. Each new
table partition resides on the same volume as its original source-table counterpart.
The new table partitions do not inherit partition names from the original table. Instead,
Neoview SQL generates new names based on the physical file location.
If you specify the LIKE clause and the PARTITION file-option, you cannot specify
WITH PARTITIONS.
Considerations for CREATE TABLE
The following subsections provide considerations for various CREATE TABLE options:
• “Considerations for CREATE SET TABLE” (page 78)
• “Considerations for CREATE VOLATILE TABLE” (page 79)
• “Considerations for CREATE TABLE LIKE” (page 81)
• “Considerations for LOAD IF EXISTS of CREATE TABLE AS” (page 84)
• “Considerations for CREATE TABLE AS” (page 84)
• “Calculating Row Size” (page 86)
• “Generating Values For an IDENTITY Column” (page 87)
• “Considerations for DISK POOL” (page 91)
Considerations for CREATE SET TABLE
The CREATE SET TABLE statement allows:
• Insert-select queries to continue processing without interruption in the event of duplicate
rows in the source table.
• Data loading tools to blindly load a table without worrying about duplicate rows in the
source data.
• IDENTITY columns are supported.
For IDENTITY columns, the GENERATED BY DEFAULT AS IDENTITY option allows you to
supply the value or use the system-generated value. The GENERATED ALWAYS AS IDENTITY
option provides system-generated unique values only. If the values are user-provided, duplicates
could occur. In that case, the duplicate rows are automatically ignored and no errors occur.
Restrictions for CREATE SET TABLE
• CREATE SET TABLE is applicable only on base tables and is not supported for index tables.
• A row that contains default values for added columns cannot be treated as identical if the
inserting row contains the same default values in the corresponding columns.
78 SQL Statements