User Guide > User Guide > Working with Tables > Creating Tables > Create a Table Using a Select Statement
Was this helpful?
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:
• An installation must be connected to (see Connecting to an Ingres or Actian Vector Installation).
• 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.
To create a table using a SELECT statement
1. Drill down into a database and open the Tables folder.
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.
3. Specify a new table name.
Names must follow Object Naming Rules.
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.
10. Click OK.
The new table is created and its node is displayed in the list of user tables in the Instance Explorer.
New Table from Select Dialog
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:
• Tables & Views
• Columns
• Additional SQL
These pages are described in the following sections.
Tables & Views Page
This page lists existing tables and views with the following options:
Name
Specifies the name of the table you are creating
Include System Objects
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_.
Tables tab
Lists the user-created tables in the database. If Include System Objects is selected, this tab also lists system tables in the database.
Views tab
Lists the user-created views in the database. If Include System Objects is selected, this tab also lists system views in the database.
Script results to new query window
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).
Columns Page
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:
SELECT a,b,c from table...
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.
Additional SQL Page
This page lets you qualify your query statement and see how it is formed. It contains the following areas and options:
Where Clause/Additional SQL
Lets you qualify the SELECT statement with qualifiers such as WHERE
Create Statement
Displays the CREATE TABLE SQL statement that is being built, based on your selections on the previous pages
OK button
Attempts to create the table using the information entered on the dialog. If successful, the new table node is displayed in the Instance Explorer.
Cancel button
Cancels the creation of the view and closes the dialog
Last modified date: 06/06/2024