15.10 - WITH DATA - Teradata Database

The data for the source table or subquery is to be copied to a new target table. You cannot create global temporary tables using the WITH DATA option.

Indicate column subsets using a subquery. Otherwise, the operation copies all column definitions and data to the target table.

WITH NO DATA

None of the data from the source table or query expression are to be copied to a new table based on its definition.

All (or a subset you define) of the table definitions for source_table_name, but none of its data, are to be copied to a new table.

Indicate column subsets using a subquery. Otherwise, the operation copies all column definitions to the target table.

Example: AS … WITH DATA With Column Constraints

This CREATE TABLE … AS … WITH DATA request specifies column constraints. Note the absence of referential integrity constraints, which are not valid for AS clauses.

The request also copies the data from the selected columns into target_table.

Because the request specifies a subquery and no explicit table kind is specified, the table kind of target_table defaults to the session mode default, not to the table kind of subquery_table.

     CREATE TABLE target_table (
       column_1 NOT NULL DEFAULT 0,
       column_2)
     AS (SELECT column_x, column_y
         FROM subquery_table )
     WITH DATA;

Example: AS … WITH DATA With Explicit Column Names

This CREATE TABLE … AS … WITH DATA request specifies column names in target_table that differ from their names in source_table. The request also copies the data from the selected columns into target_table.

Because the request specifies a subquery and no explicit table kind is specified, the table kind of target_table defaults to the session mode default, not to the table kind of subquery_table. See the CREATE TABLE (AS Clause) topic “Target Table Kind Defaults” in SQL Data Definition Language - Detailed Topics, B035-1184).

     CREATE TABLE target_table (
       column_x,
       column_y)
     AS (SELECT column_1, column_2
         FROM subquery_table )
     WITH DATA;

Example: AS … WITH NO DATA With Explicit Column Names

This CREATE TABLE … AS … WITH NO DATA request specifies column names for target_table as column alias names in the subquery.

Because the request specifies a subquery and no explicit table kind is specified, the table kind of target_table defaults to the session mode default, not to the table kind of subquery_table.

     CREATE TABLE target_table AS (
       SELECT column_x AS column_1, column_y AS column_2
       FROM subquery_table )
     WITH NO DATA;

Example: AS … WITH NO DATA With Explicit Column Attributes

This CREATE TABLE … AS … WITH NO DATA request specifies column attributes and uses an expression to define a column in target_table.

Because the request specifies a subquery and no explicit table kind is specified, the table kind of target_table defaults to the session mode default, not to the table kind of subquery_table.

     CREATE TABLE target_table (
       column_1 TITLE 'max_x',
       column_2)
     AS (SELECT MAX(column_x), column_y
         FROM subquery_table
         GROUP BY 2 )
     WITH NO DATA;

Example: AS … WITH NO DATA With Explicit Column Attributes

This example creates the same target_table as above, but codes it differently.

Because the request specifies a subquery and does not specify an explicit table kind, the table kind of target_table defaults to the session mode default, not to the table kind of subquery_table.

     CREATE TABLE target_table AS (SELECT MAX(column_x) 
                                        (TITLE 'max_x' NAMED column_1),
                                         column_y AS column_2
                                   FROM subquery_table
                                   GROUP BY 2)
     WITH NO DATA;

Example: AS … WITH DATA With Unnamed Expressions

This CREATE TABLE … AS … WITH DATA request creates target_table from a subquery having unnamed expressions that define both columns for target_table. The affected columns are named explicitly in the target_table definition to compensate. The request also copies the data from the selected columns into target_table.

Because the request specifies a subquery and no explicit table kind is specified, the table kind of target_table defaults to the session mode default, not to the table kind of subquery_table.

     CREATE TABLE target_table (
       column_x,
       column_y)
     AS (SELECT column_x + 1, column_y + 1
         FROM subquery_table )
     WITH DATA;

Example: AS … WITH DATA With an Unnamed Expression

This CREATE TABLE … AS … WITH DATA request creates target_table from a subquery having an unnamed aggregate expression. Note that the affected column is named explicitly in the target_table definition to compensate. The request also copies the data from the selected columns into target_table.

Because the request specifies a subquery and no explicit table kind is specified, the table kind of target_table defaults to the session mode default, not to the table kind of subquery_table.

     CREATE TABLE target_table (
       column_x,
       column_y)
     AS (SELECT MAX(column_x), column_y
         FROM subquery_table
         GROUP BY 2 )
     WITH DATA;

Example: AS … WITH DATA Where Column Attributes Differ Between Source and Target Table Definitions

This example shows that when you specify explicit column descriptors that are different from those defined in the source table, the system does not copy the old column attributes, but instead defines the new table with the attributes specified in the target table definition.

Suppose you create the following simple table.

     CREATE TABLE source (
       x INTEGER NOT NULL,
       y INTEGER DEFAULT 0);

You then decide to create a new table with data based on the definition of source, so you submit a CREATE TABLE … AS … WITH DATA request like the following.

     CREATE TABLE target (
       a,
       b NOT NULL) AS (SELECT *
                       FROM source) 
     WITH DATA;

You then submit a SHOW TABLE request on target to see what its column descriptors are:

     SHOW TABLE target;
     *** Text of DDL statement returned.
      *** Total elapsed time was 1 second.
     -----------------------------------------------------------------
     CREATE SET TABLE user_name.target ,NO FALLBACK ,
          NO BEFORE JOURNAL,
          NO AFTER JOURNAL,
          CHECKSUM = DEFAULT
          (
           a INTEGER,
           b INTEGER NOT NULL)
     PRIMARY INDEX ( a );

The NOT NULL attribute is not copied from column source.x to column target.a, and the DEFAULT 0 attribute is not copied from column source.y to column target.b. Instead, target.a has no column attribute because none is specified for it in its table definition, and target.b has the column attribute NOT NULL, as specified in the table definition for target.b, rather than the attribute DEFAULT 0 as is specified for source.y.

Example: Global Temporary Table … AS … WITH NO DATA

This CREATE TABLE … AS … WITH NO DATA request creates a new global temporary table using all the selected column definitions with default definitions for all table options clause attributes.

Because the request specifies a subquery and no explicit table kind is specified, the table kind of target_table defaults to the session mode default, not to the table kind of subquery_table.

    CREATE GLOBAL TEMPORARY TABLE target_table AS (SELECT *
                                                   FROM subquery_table)
    WITH NO DATA;

Example: Create Volatile Table … AS … WITH DATA

This CREATE TABLE … AS … WITH DATA request creates a new volatile table using all the column definitions and data from source_table.

The table kind of target_table defaults to the table kind of source_table. See the CREATE TABLE (AS Clause) topic “Target Table Kind Defaults” in SQL Data Definition Language - Detailed Topics, B035-1184.

    CREATE VOLATILE TABLE target_table AS source_table
    WITH DATA;