WITH NO DATA - Advanced SQL Engine - Teradata Database

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 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: 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;