Creates a new table.

Syntax

---

CREATE [TEMPORARY] TABLE <Name>
[[(
<ColumnName> <ColumnDefinition>|
LIKE <LikeDefinition>|
[CONSTRAINT <ConstraintName>] <ConstraintDefinition>]
[,<ColumnName> <ColumnDefinition>|
LIKE <LikeDefinition>|
[CONSTRAINT <ConstraintName>] <ConstraintDefinition>]
)]
[AS <QueryExpression> WITH DATA|WITH NO DATA]
[FROM PUBLISHED UPDATES [TABLES <TableName> [,<TableName>]]] | [FROM UPDATES <UpdateName> IN STORE <StoreName>]]
[DESCRIPTION <Description>]
[VERSION <VersionNumber>]
[READONLY|READWRITE]
[ENCRYPTED|UNENCRYPTED]
[INDEX PAGE SIZE <IndexPageSize>]
[BLOB BLOCK SIZE <BLOBBlockSize>]
[PUBLISH BLOCK SIZE <PublishBlockSize>]
[PUBLISH COMPREESSION <Compression>]
[MAX ROW BUFFER SIZE <MaxRowBufferSize>]
[MAX INDEX BUFFER SIZE <MaxIndexBufferSize>]
[MAX BLOB BUFFER SIZE <MaxBLOBBufferSize>]
[MAX PUBLISH BUFFER SIZE <MaxPublishBufferSize>]
[ATTRIBUTES <CustomAttributes>]

<ColumnDefinition> = 

<DataType>
[COMPRESSION <Compression>]
[GENERATED <GenerationOptions>|COMPUTED <ComputationOptions>|
DEFAULT <DefaultExpression>]
[NOT NULL [ERROR CODE <ErrorCode> MESSAGE <ErrorMessage>]]
[<ColumnConstraintDefinition>] [<ColumnConstraintDefinition>]
[DESCRIPTION <Description>]

<ErrorCode> = Any user-defined (10000-High(INTEGER)) error code

<DataType> =

CHARACTER|CHAR [(<Length>]) [<CollationName>]
CHARACTER VARYING|VARCHAR [(<Length>]) [<CollationName>]
GUID
BYTE [(<LengthInBytes>])
BYTE VARYING|VARBYTE [(<LengthInBytes>])
BINARY LARGE OBJECT|BLOB
CHARACTER LARGE OBJECT|CLOB [<CollationName>]
BOOLEAN|BOOL
SMALLINT
INTEGER|INT
BIGINT
FLOAT [(<Precision>,<Scale>)]
DECIMAL|NUMERIC [(<Precision>,<Scale>)]
DATE
TIME
TIMESTAMP
INTERVAL YEAR [TO MONTH]
INTERVAL MONTH
INTERVAL DAY [TO HOUR|MINUTE|SECOND|MSECOND]
INTERVAL HOUR [TO MINUTE|SECOND|MSECOND]
INTERVAL MINUTE [TO SECOND|MSECOND]
INTERVAL SECOND [TO MSECOND]
INTERVAL MSECOND

<Compression> = 0..9

<GenerationOptions> =
ALWAYS AS <GenerateExpression>|
ALWAYS AS IDENTITY (START WITH <SeedValue>, INCREMENT BY <IncrementValue>)|
BY DEFAULT AS IDENTITY (START WITH <SeedValue>, INCREMENT BY <IncrementValue>)

<GenerateExpression> =
Any valid SQL expression that does not include any sub-queries

<ComputationOptions> =
ALWAYS AS <ComputeExpression>

<ComputeExpression> =
Any valid SQL expression that does not include any sub-queries
or user-defined function references

<DefaultExpression> =
Any valid SQL expression that does not include any sub-queries

<ColumnConstraintDefinition> =

CHECK <CheckExpression>
PRIMARY KEY|
UNIQUE|
REFERENCES <TableName> [(<ColumnName> [,<ColumnName>])]
   [ON UPDATE RESTRICT|ON DELETE RESTRICT]
[ERROR CODE <ErrorCode> MESSAGE <ErrorMessage>]

<CheckExpression> =
Any valid SQL expression that does not include any sub-queries

<ErrorCode> = Any user-defined (10000-High(INTEGER)) error code

<LikeDefinition> =

<TableName>
[INCLUDING|EXCLUDING IDENTITY]
[INCLUDING|EXCLUDING DEFAULTS]
[INCLUDING|EXCLUDING GENERATED]

<ConstraintDefinition> = 

CHECK <CheckExpression>
PRIMARY KEY (<ColumnName> [,<ColumnName>])|
UNIQUE (<ColumnName> [,<ColumnName>])|
FOREIGN KEY (<ColumnName> [,<ColumnName>])
   REFERENCES <TableName> [(<ColumnName> [,<ColumnName>])]
   [ON UPDATE RESTRICT|ON DELETE RESTRICT]
[ERROR CODE <ErrorCode> MESSAGE <ErrorMessage>]
[DESCRIPTION <Description>]

<CheckExpression> =
Any valid SQL expression that does not include any sub-queries

<ErrorCode> = Any user-defined (10000-High(INTEGER)) error code

Usage

---

Use this statement to create a new table. Use the TEMPORARY clause to specify that the table should be created as a local temporary table that is only visible to the current session. You may only use the following DDL statements on temporary tables:

   DROP TABLE
   RENAME TABLE
   EMPTY TABLE
   CREATE INDEX
   CREATE TEXT INDEX
   DROP INDEX
   RENAME INDEX

Use the READONLY clause to specify that a table is always read-only. Doing so can improve multi-user performance on a table because ElevateDB will not need to perform any locking on such a table.

FROM PUBLISHED UPDATES and FROM UPDATES Clauses

Use the FROM PUBLISHED UPDATES or FROM UPDATES version of the CREATE TABLE statement to create a table that contains the contents of pending published updates that have not been saved, or the contents of an existing replication update file in a store. These two clauses are mutually-exclusive, and only one can be used at a time.

The format of the created table will be as follows:

"TableName" VARCHAR(40) COLLATE "ANSI_CI",
"UpdateType" VARCHAR(15) COLLATE "ANSI_CI",
"UpdateTimeStamp" TIMESTAMP,
"Manifest" CLOB COLLATE "ANSI_CI",
"KeyData" CLOB COLLATE "ANSI_CI",
"RowData" CLOB COLLATE "ANSI_CI"

The UpdateType column will contain one of the following values:


The Manifest column contains a CRLF-delimited list of published table IDs that serve to tell ElevateDB which published tables have loaded this update already.

With the PUBLISHED UPDATES clause, you can further limit the published tables that are used to generate the table by using the TABLES clause. When using the TABLES clause, only the pending published updates for the specified tables will be included in the created table.

For more information on replication, please see the Replication topic.

Examples

---

-- The following statement creates the Customer table

CREATE TABLE "Customer"
(
"ID" INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 0, INCREMENT BY 1),
"Name" VARCHAR(30) COLLATE "ANSI_CI",
"Address1" VARCHAR(40) COLLATE "ANSI_CI",
"Address2" VARCHAR(40) COLLATE "ANSI_CI",
"City" VARCHAR(30) COLLATE "ANSI_CI",
"State" CHAR(2) COLLATE "ANSI_CI",
"Zip" CHAR(10) COLLATE "ANSI_CI",
"CreatedOn" TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT "ID_PrimaryKey" PRIMARY KEY ("ID"),
CONSTRAINT "ID_Check" CHECK (ID IS NOT NULL),
CONSTRAINT "Name_Check" CHECK (Name IS NOT NULL)
)

-- The following statement creates a temporary table containing
-- the contents of an update file

CREATE TEMPORARY TABLE "MainOfficeUpdates"
FROM UPDATES "MainOffice-2010-12-14 17-57-12.0306"
IN STORE MainUpdates

Required Privileges

---

The current user must be granted the CREATE privilege on the current database in order to execute this statement. Please see the User Security topic for more information.

SQL 2003 Standard Deviations

---

This statement deviates from the SQL 2003 standard in the following ways: