8. SQL Statements : CREATE TABLE : Syntax
 
Share this page                  
Syntax
The CREATE TABLE statement has the following format:
[EXEC SQL] CREATE TABLE [schema.] table_name
              (column_specification {, column_specification }
              [, [CONSTRAINT constraint_name] table_constraint
              {, [CONSTRAINT constraint_name] table_constraint}])
              [WITH with_clause]
The CREATE TABLE...AS SELECT statement (which creates a table and loads rows from another table) has the following format:
[EXEC SQL] CREATE TABLE table_name
              (column_name {, column_name}) AS
                            [WITH common_table_expression] subselect
                            {UNION [ALL]
                            subselect}
              [WITH with_clause]
table_name
Defines the name of the new table, which must be a valid object name.
column_specification
Defines the characteristics of the column, as described in Column Specification (see page Column Specification--Define Column Characteristics).
table_constraint
Specifies the table-level constraint as described in Column-Level Constraints and Table-Level Constraints (see page Column-Level Constraints and Table-Level Constraints).
with_clause
Consists of a comma-separated list of one or more of the following options, described in detail in With_Clause for Create Table (see page With_Clause for Create Table):
• LOCATION = (location_name {, location_name})
• [NO]JOURNALING
• [NO]DUPLICATES
• PAGE_SIZE = n
• SECURITY_AUDIT = (audit_opt {, audit_opt})
• SECURITY_AUDIT_KEY = (column)
• NOPARTITION | PARTITION = ( partitioning-scheme )
• [NO]AUTOSTRUCT
• COMPRESSION[= ([[NO]KEY] [,[NO]DATA])] | NOCOMPRESSION
• ALLOCATION = n
• EXTEND = n
• PRIORITY = n
• ENCRYPTION=AES128|AES192|AES256, [AESKEY=hex-aes-key,] PASSPHRASE='encryption-passphrase'
Additional options on the With_Clause for Create Table...as Select (see page With_Clause for Create Table...As Select) are as follows:
• STRUCTURE = HASH | HEAP | ISAM | BTREE
• KEY = (column_name {, column_name})
• FILLFACTOR = n
• MINPAGES = n
• MAXPAGES = n
• LEAFFILL = n
• NONLEAFFILL = n
AS [WITH common_table_expression] subselect
(Valid in CREATE TABLE AS SELECT only.)
Specifies a SELECT clause, described in detail in Select (interactive) (see page SELECT (interactive)). Also see Using Create Table...As Select (see page Using Create Table...As Select).
Note:  Subselect cannot be used when creating a table in one or more raw locations--that is, CREATE TABLE raw_table AS SELECT... WITH LOCATION = (raw_loc).
The subselect can be preceded by a common table expression (see page WITH (common_table_expression)).