17.00 - WITH DATA - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Release Date
September 2020
Content Type
Programming Reference
Publication ID
B035-1144-170K
Language
English (United States)

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.

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 Teradata Vantage™ - 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 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: 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 Teradata Vantage™ - SQL Data Definition Language Detailed Topics , B035-1184 .

    CREATE VOLATILE TABLE target_table AS source_table
    WITH DATA;