Filter
Top Products
Working with tables
120
By internally executing the COMMIT statement before creating the table,
Adaptive Server IQ makes permanent all previous changes to the database.
There is also a
COMMIT after the table is created.
For a full description of the
CREATE TABLE statement, see “CREATE TABLE
statement” in the Adaptive Server IQ Reference Manual. For information about
building constraints into table definitions using
CREATE TABLE, see Chapter
7, “Ensuring Data Integrity”.
Specifying data types
When you create a table, you specify the type of data that each column holds.
You can also define customized data types for your database. In the Adaptive
Server IQ Reference Manual, see “SQL Data Types” for a list of supported data
types, or see the
CREATE DOMAIN statement for details on how to create a
customized data type.
Types of tables
Adaptive Server IQ recognizes four types of tables:
• Base tables
• Local temporary tables
• Global temporary tables
• Join virtual tables
Base tables are
permanent
Base tables are sometimes called main or permanent tables, because they are
stored in the main IQ Store, and are a permanent part of the database, until you
drop them explicitly. Base tables and the data in them are accessible to all users
who have the appropriate permissions. The
CREATE TABLE statement shown
in the previous example creates a base table.
Creating temporary
tables
There are two types of temporary tables, global and local.
You create a global temporary table, using the
GLOBAL TEMPORARY option
of
CREATE TABLE, or by specifying in the Sybase Central table editor that this
is a temporary table. When you create a global temporary table, it exists in the
database until it is explicitly removed by a
DROP TABLE statement.
A database contains only one definition of a global temporary table, just as it
does for a base table. However, each user has a separate instance of the data in
a global temporary table. Those rows are visible only to the connection that
inserts them. They are deleted when the connection ends.
To select into a temporary table, use syntax like the following:
SELECT * INTO #TableTemp FROM lineitem