CREATE TABLE statement can use CTAS syntax

When operating in NPS compatibility mode, the AS clause of a CREATE TABLE statement can use the same syntax as the corresponding clause of a Netezza CREATE TABLE AS command (sometimes referred to as a CTAS command).

Note: When the EXPLAIN mode is ON and a CTAS statement is explained, the target table is created but no data is loaded into it.
Whether you are operating in NPS compatibility mode depends on the setting of the SQL_COMPAT global variable:
  • When SQL_COMPAT='NPS', the AS clause of a CREATE TABLE statement can use the following syntax:
    Read syntax diagramSkip visual syntax diagram AS fullselect
  • Otherwise, the AS clause of a CREATE TABLE statement must use the following syntax:
    Read syntax diagramSkip visual syntax diagram AS ( fullselect ) WITH DATAWITH NO DATA
    If WITH DATA is specified, the table is populated with the result of the query that is specified in the fullselect statement. If WITH NO DATA is specified, the fullselect statement is used only to define the table, but the table is not populated with the results of the query.