CREATE TABLE AS SELECT (SQL)
Synopsis
CREATE TABLE table-name AS query [shard-key] [WITH table-option]
Arguments
table-name | The name of the table to be created, specified as a valid identifier. A table name can be qualified (schema.table), or unqualified (table). An unqualified table name takes the default schema name. |
query | A SELECT query that supplies the column definitions and column data for the new table. This query can specify a table, a view, or multiple joined tables. However, it cannot contain ? parameters like regular SELECT statements. |
shard-key | Optional — the shard key definition, consisting of the SHARD keyword by itself or followed by additional shard key definition syntax. |
WITH table-option | Optional — A comma-separated list of one or more table options, such as the %CLASSPARAMETER keyword followed by a name and associated literal. |
Description
The CREATE TABLE AS SELECT command creates a new table by copying the column definitions and column data from an existing table (or tables), as specified in a SELECT query. The SELECT query can specify any combination of tables or views.
CREATE TABLE AS SELECT copies from an existing table definition. Use the CREATE TABLE command to specify a new table definition.
A copy table operation can also be invoked using the QueryToTable()Opens in a new tab method call:
DO $SYSTEM.SQL.Schema.QueryToTable(query,table-name,0)
Copying Data Definition
-
CREATE TABLE AS SELECT copies column definitions from the query table. To rename copied columns specify a column alias in the query.
CREATE TABLE AS SELECT can copy column definitions from multiple tables if the query specifies joined tables.
-
CREATE TABLE AS SELECT always defines the RowID as hidden.
-
If the source table has a hidden RowID, CREATE TABLE AS SELECT does not copy source table RowID, but creates a new RowID column for the created table. Copied rows are assigned new sequential RowID values.
-
If the source table has a public (non-hidden) RowID, or if the query explicitly selects a hidden RowID, CREATE TABLE AS SELECT creates a new RowID column for the table. The source table RowID is copied into the new table as an ordinary BigInt field that is not hidden, not unique, and not required. If the source table RowID is named “ID”, the new table’s RowID is named “ID1”.
-
-
If the source table has an IDENTITY field, CREATE TABLE AS SELECT copies it and its current data as an ordinary BIGINT field for non-zero positive integers that is neither unique nor required.
-
CREATE TABLE AS SELECT defines an IDKEY index. It does not copy indexes associated with copied column definitions.
-
CREATE TABLE AS SELECT does not copy any column constraints: it does not copy NULL/NOT NULL, UNIQUE, Primary Key, or Foreign Key constraints associated with a copied column definition.
-
CREATE TABLE AS SELECT does not copy a Default restriction or value associated with a copied column definition.
-
CREATE TABLE AS SELECT does not copy a COMPUTECODE data constraint associated with a copied column definition.
-
CREATE TABLE AS SELECT does not copy a %DESCRIPTION string associated with copied table or column definition.
Privileges
The CREATE TABLE AS SELECT command is a privileged operation. The user must have %CREATE_TABLE administrative privilege to execute CREATE TABLE AS SELECT. Failing to do so results in an SQLCODE -99 error with the %msg User 'name' does not have %CREATE_TABLE privileges. You can use the GRANT command to assign %CREATE_TABLE privileges to a user or role, if you hold appropriate granting privileges. Administrative privileges are namespace-specific. For further details, refer to Privileges.
The user must have SELECT privilege on the table specified in the query.
Table Name
A table name can be qualified or unqualified.
-
An unqualified table name has the following syntax: tablename; it omits schema (and the period (.) character). An unqualified table name takes the default schema name. The initial system-wide default schema name is SQLUser, which corresponds to the default class package name User. Schema search path values are ignored.
The default schema name can be configured.
To determine the current system-wide default schema name, use the $SYSTEM.SQL.Schema.Default()Opens in a new tab method.
-
A qualified table name has the following syntax: schema.tablename. It can specify either an existing schema name or a new schema name. Specifying an existing schema name places the table within that schema. Specifying a new schema name creates that schema (and associated class package) and places the table within that schema.
Table names and schema names follow SQL identifier naming conventions, subject to additional constraints on the use of non-alphanumeric characters, uniqueness, and maximum length. Names beginning with a % character are reserved for system use. By default, schema names and table names are simple identifiers, and are not case-sensitive.
InterSystems IRIS uses the table name to generate a corresponding class name. InterSystems IRIS uses the schema name is used to generate a corresponding class package name. A class name contains only alphanumeric characters (letters and numbers) and must be unique within the first 96 characters. To generate a class name, InterSystems IRIS first strips out symbol (non-alphanumeric) characters from the table name, and then generates a unique class name, imposing uniqueness and maximum length restrictions. To generate a package name, it then either strips out or performs special processing of symbol (non-alphanumeric) characters in the schema name. InterSystems IRIS then generates a unique package name, imposing uniqueness and maximum length restrictions. For further details on how package and class names are generated from schema and table names, refer to Table Names and Schema Names.
You can use the same name for a schema and a table. You cannot use the same name for a table and a view in the same schema.
A schema name is not case-sensitive; the corresponding class package name is case-sensitive. If you specify a schema name that differs only in case from an existing class package name, and the package definition is empty (contains no class definitions). InterSystems IRIS reconciles the two names by changing the case of the class package name. For further details on schema names, refer to Table Names and Schema Names.
InterSystems IRIS supports 16-bit (wide) characters for table and column names. For most locales, accented letters can be used for table names and the accent marks are included in the generated class name. The following example performs validation tests on an SQL table name:
TableNameValidation
SET tname="MyTestTableName"
SET x=$SYSTEM.SQL.IsValidRegularIdentifier(tname)
IF x=0 {IF $LENGTH(tname)>200
{WRITE "Tablename is too long" QUIT}
ELSEIF $SYSTEM.SQL.IsReservedWord(tname)
{WRITE "Tablename is reserved word" QUIT}
ELSE {
WRITE "Tablename contains invalid characters",!
SET nls=##class(%SYS.NLS.Locale).%New()
IF nls.Language [ "Japanese" {
WRITE "Japanese locale cannot use accented letters"
QUIT }
QUIT }
}
ELSE { WRITE tname," is a valid table name"}
The Japanese locale does not support accented letter characters in identifiers. Japanese identifiers may contain (in addition to Japanese characters) the Latin letter characters A-Z and a-z (65–90 and 97–122), the underscore character (95), and the Greek capital letter characters (913–929 and 931–937). The nls.Language test uses [ (the Contains operator) rather than = because there are different Japanese locales for different operating system platforms.
Existing Table
To determine if a table already exists in the current namespace, use $SYSTEM.SQL.Schema.TableExists("schema.tname")Opens in a new tab.
By default, when you try to create a table that has the same name as an existing table InterSystems IRIS rejects the create table attempt and issues an SQLCODE -201 error. To determine the current system-wide configuration setting, call $SYSTEM.SQL.CurrentSettings()Opens in a new tab, which displays a Allow DDL CREATE TABLE or CREATE VIEW for existing table or view setting. The default is 0; this is the recommended setting for this option. If this option is set to 1, InterSystems IRIS deletes the class definition associated with the table and then recreates it. This is much the same as performing a DROP TABLE, deleting the existing table and then performing the CREATE TABLE. In this case, it is strongly recommended that the $SYSTEM.SQL.CurrentSettings()Opens in a new tab, Does DDL DROP TABLE delete the table's data? value be set to 1 (the default). Refer to DROP TABLE for further details.
From the Management Portal, System Administration, Configuration, SQL and Object Settings, SQL you can set this option (and other similar create, alter, and drop options) system-wide by selecting the Ignore redundant DDL statements check box.
WITH table-option
The optional WITH clause can be specified after the SELECT query. The WITH clause can contain a comma-separated list of %CLASSPARAMETER clauses.
The %CLASSPARAMETER keyword enables you to define a class parameter as part of the CREATE TABLE AS SELECT command. A class parameter is always defined as a constant value. The %CLASSPARAMETER keyword is followed by the class parameter name, an optional equal sign, and the literal value (a string or number) to assign to that class parameter.
You can specify multiple %CLASSPARAMETER keyword clauses, defining one class parameter per clause. Multiple %CLASSPARAMETER clauses are separated by commas.
For example, by default CREATE TABLE AS SELECT creates an IDKEY index for the created table with a generated Global name, such as ^EPgS.D8T6.1; additional indexes use the same global name with a unique integer suffix. The following example shows how to specify an explicit Global name for the IDKEY index and future additional indexes:
CREATE TABLE Sample.YoungPeople
AS SELECT Name,Age
FROM Sample.People
WHERE Age<21
WITH %CLASSPARAMETER DEFAULTGLOBAL = '^GL.UNDERTWENTYONE'
For further details, refer to WITH Clause and %CLASSPARAMETER Keyword in the CREATE TABLE reference page.