Commands

CREATE TABLE

The CREATE TABLE statement creates a new table in the connected database. The new table is physically added to the database and an entry is made into the Environment Access Cache to make Omnidex aware of this table. This allows the current process to perform queries and other data manipulation on the new table. The table created remains in place until a DROP TABLE command is issued.

This newly created table is only accessible to the Omnidex process in which it was created. If the table is not dropped before this process terminates, the table will remain in place in the database and can only be removed using the appropriate DBMS tools.

To make a table created with the CREATE TABLE command available in the future to other Omnidex processes, add the table definition for the new table to the environment source file, recompile it and reinstall the indexes. Indexes can also be installed on the new table after it has been permanently added to the environment catalog.

The CREATE TABLE command should be followed by one or more INSERT INTO commands to populate the new table with data.

The combination of a CREATE TABLE command followed by an INSERT INTO command is similar to a SELECT INTO command. However, a SELECT INTO command creates a new table and therefore cannot add data to an existing table.

A DROP TABLE command can be used to drop a table created with either a CREATE TABLE command or a SELECT INTO command.

Syntax

CREATE [GLOBAL TEMPORARY | LOCAL TEMPORARY] TABLE table [PHYSICAL physical] (column [,column...])

CREATE
Required

[GLOBAL TEMPORARY | LOCAL TEMPORARY]
Optional. Specify if the table is a global temporary table or a local temporary table. Temporary tables are automatically dropped by the DBMS when the current user session ends. GLOBAL means the table is accessible to all active user sessions and LOCAL means the table is accessible only the the user session in which it was created.

TABLE table
Required. table is the name of the table to be created as it will be referred to in subsequent queries.

physical
Optional. The physical file name to be created. If omitted, the file will be created in the same location as the environment catalog and will be named according to the table parameter.

column
Required. One or more column specifications, comma separated. column-name datatype (length)

Options

Example

This example creates an empty table named CUSTS using a CREATE TABLE command, inserts some rows into the table, runs a select statement against that table and then drops the table.

>CREATE TABLE CUSTS (CUSTOMER CHAR LENGTH 40)
Table CUSTS created
>

Insert a row using the INSERT INTO command.

>INSERT INTO CUSTS VALUES ('JANE SMITH')
1 row inserted into CUSTS
>

>INSERT INTO CUSTS VALUES ('JOHN SIMPSON')
1 row inserted into CUSTS
>

Select rows from the new table using the SELECT command.

>SELECT * FROM CUSTS

CUSTOMER
----------------------------------------
JANE SMITH
JOHN SIMPSON

Note that because the new table is not indexed, selects are preformed by serial read.

Drop the table using the DROP TABLE command.

>DROP TABLE CUSTS
Table CUSTS dropped
>

Top