OpenSQL Reference Guide > OpenSQL Reference Guide > Extended Statements > CREATE TABLE (extended)
Was this helpful?
CREATE TABLE (extended)
Valid in: SQL, ESQL, OpenAPI, ODBC, JDBC, .NET
The CREATE TABLE statement creates a base table. The table is owned by the user who issues the statement.
If you use the CREATE TABLE...AS SELECT syntax, the table that you create will contain a subset of the columns and values in existing tables specified by the subselect.
The CREATE TABLE statement has the following format:
[EXEC SQL] CREATE TABLE table_name
              [(column_specification {, column_specification }
               (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 load rows from another table) has the following format:
[EXEC SQL] CREATE TABLE table_name
              (column_name {, column_name}) AS
                            subselect
                            {UNION [ALL]
                            subselect}
              [WITH with_clause]
table_name
Specifies the name of the new table, and must be a valid object name (see Rules for Naming Objects on page 4).
column_specification
Specifies the characteristics of the column, as described in Column Specifications.
subselect
Specifies a SELECT clause, described in SELECT.
table_constraint
Specifies a table constraint (as described in Constraints), as one or more of the following:
UNIQUE (column_name {, column_name}) [WITH constraint_with_
clause]
PRIMARY KEY (column_name {, column_name}) [WITH constraint_with_clause]
FOREIGN KEY (column_name {, column_name})
REFERENCES [schema.]table_name [(column_name {, column_name})]
    [WITH constraint_with_clause]
WITH with_clause
Specifies WITH clause options.
For an overview of the Enterprise Access product WITH clause, see DBMS Extensions on page 24.
For valid WITH clause options for a specific Enterprise Access, see your Enterprise Access product guide.
For valid WITH clause options for the Ingres DBMS, see the SQL Reference Guide.
Last modified date: 01/30/2023