Create a Table Using a Select Statement

You can create a new Actian database table from a SELECT statement, gathering data from various other databases and tables.

Before you can create a table from a select statement:

• A user table node in the database must be selected in the Instance Explorer.

You create a table using the New Table from Select Dialog.

2. Right-click on a user table node and select New Table From Select on the context menu.

The New Table from Select dialog opens, displaying the Tables & Views page.

4. On the Tables tab, select any user tables you want to include in the new table. If you want to include system tables, check the Include System Objects option and select the system tables you want to include.

5. On the Views tab, select any user views you want to include in the new table. If you want to include system views, check the Include System Objects option and select the system views you want to include.

6. Click the Columns page link (see New Table from Select Dialog).

7. If you want to include only particular columns, select those columns in the list.

8. Click the Additional SQL page link (see New Table from Select Dialog).

9. Specify any query qualifiers in the Where Clause/Additional SQL text box.

The CREATE TABLE statement is built in the area at the bottom of the dialog.

The new table is created and its node is displayed in the list of user tables in the Instance Explorer.

The New Table from Select dialog lets you create a new table from a SELECT statement.

You open the New Table from Select dialog by right-clicking on a database’s Tables folder or a user table node in the Instance Explorer and selecting New Table from Select on the context menu.

For more information, see Create a Table Using a Select Statement.

The New Table From Select dialog contains the following pages:

These pages are described in the following sections.

This page lists existing tables and views with the following options:

Specifies whether system tables and views in the database should be included on the Tables and Views tabs. System object names begin with “$ingres”.ii_.

Lists the user-created tables in the database. If Include System Objects is selected, this tab also lists system tables in the database.

Lists the user-created views in the database. If Include System Objects is selected, this tab also lists system views in the database.

Specifies that the query text is copied to a query document instead of being executed. You may then edit the SQL before executing it (see Execute a Query).

This page lets you specify whether to include all columns or only specific columns from tables and views selected on the Tables & Views page. If you choose to specify particular columns, you may change the name of the column in the new table by entering the new name in the Column Alias field.

The output columns of the SELECT are matched, in the order they appear, with the column names specified. The column names are used in the new table. For example, if your SELECT statement contains:

and you specify column aliases apple, banana, cherry, the new table will contains columns named apple, banana, cherry. If there are more columns in the result than you have provided, the remaining columns in the result will not be renamed but will retain the names from their source.

Note: Director validates the names of the columns to ensure they are valid object names (see Object Naming Rules). All other validation is left to the DBMS.

This page lets you qualify your query statement and see how it is formed. It contains the following areas and options:

Lets you qualify the SELECT statement with qualifiers such as WHERE

Displays the CREATE TABLE SQL statement that is being built, based on your selections on the previous pages

Attempts to create the table using the information entered on the dialog. If successful, the new table node is displayed in the Instance Explorer.