CREATE TABLE … AS Syntax | Teradata Vantage - CREATE TABLE … AS Syntax - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Published
September 2020
Language
English (United States)
Last Update
2021-01-23
dita:mapPath
wgr1555383704548.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1144
lifecycle
previous
Product Category
Teradata Vantage™
{ CREATE table_kind TABLE | CT } table_specification
  [ table_option [,...] ]
  ( attribute [,...] )
  AS_clause
  [ , index [[,]...] ]
  [ table_preservation ][;]
table_kind
[ SET | MULTISET ] [ GLOBAL TEMPORARY | VOLATILE ]
table_specification
[ database_name. | user_name. ] table_name
table_option
{ MAP = map_name [COLOCATE USING colocation_name | 
  [NO] FALLBACK [PROTECTION] | 
  WITH JOURNAL TABLE = table_specification | 
  [NO] LOG | 
  [ NO | DUAL ] [BEFORE] JOURNAL | 
  [ NO | DUAL | LOCAL | NOT LOCAL ] AFTER JOURNAL | 
  CHECKSUM = { DEFAULT | ON | OFF } | 
  FREESPACE = integer [PERCENT] |
  mergeblockratio |
  datablocksize |
  blockcompression |  
  isolated_loading
}
attribute
{ column_specification |
  [ COLUMN | ROW ] ( column_specification [,...] ) [ [NO] AUTOCOMPRESS ] |
  table_constraint
}
AS_clause
AS source_table [ subquery_clause ] WITH [NO] DATA [ AND [NO] STATISTICS ]
index
[UNIQUE] PRIMARY INDEX [index_name] ( index_column_name [,...] ) | 
NO PRIMARY INDEX |
PRIMARY AMP [INDEX] [index_name] ( index_column_name [,...] ) |
PARTITION BY { partitioning_level | ( partitioning_level [,...] ) } |
UNIQUE INDEX [ index_name ] [ ( index_column_name [,...] ) ] [loading] |
INDEX [index_name] [ALL] ( index_column_name [,...] ) [ordering] [loading] 
[,...]
table_preservation
ON COMMIT { DELETE | PRESERVE } ROWS
mergeblockratio
{ DEFAULT MERGEBLOCKRATIO | 
  MERGEBLOCKRATIO = integer [PERCENT] | 
  NO MERGEBLOCKRATIO
}
datablocksize
DATABLOCKSIZE = {
  data_block_size [ BYTES | KBYTES | KILOBYTES ] |
  { MINIMUM | MAXIMUM | DEFAULT } DATABLOCKSIZE
}
blockcompression
BLOCKCOMPRESSION = { AUTOTEMP | MANUAL | ALWAYS | NEVER | DEFAULT }
   [, BLOCKCOMPRESSIONALGORITHM = { ZLIB | ELZS_H | DEFAULT } ]
   [, BLOCKCOMPRESSIONLEVEL = { value | DEFAULT } ]
isolated_loading
WITH [NO] [CONCURRENT] ISOLATED LOADING [ FOR { ALL | INSERT | NONE } ]
subquery_clause
AS source_table WITH [NO] DATA [ AND [NO] STATISTICS ]
column_specification
column_name [ column_data_type_attribute [...] ]
partitioning_level
  { partitioning_expression |
    COLUMN [ [NO] AUTO COMPRESS |
    COLUMN [ [NO] AUTO COMPRESS ] [ ALL BUT ] column_partition ]
  } [ ADD constant ]
loading
WITH [NO] LOAD IDENTITY
ordering
ORDER BY [ VALUES | HASH ] [ ( order_column_name ) ]
column_data_type_attribute
{ { UPPERCASE | UC } |
  [NOT] { CASESPECIFIC | CS } |
  FORMAT quotestring |
  TITLE quotestring |
  NAMED name |
  DEFAULT { number | USER | DATE | TIME | NULL } |
  WITH DEFAULT |
  CHARACTER SET server_character_set |
  [NOT] NULL |
  [NOT] AUTO COLUMN |
  compression_attribute |
  column_constraint_attribute |
  identity_column 
}
compression_attribute
{ NO COMPRESS |

  COMPRESS [ constant | ( { constant | NULL } [,...] ) ] |

  COMPRESS USING compress_UDF_name DECOMPRESS
    USING decompress_UDF_name
}
column_constraint_attribute
[ CONSTRAINT constraint_name ] 
  { UNIQUE | PRIMARY KEY | CHECK ( boolean_condition ) | references } |
  [ row_level_security_constraint_column_name [,...] ] CONSTRAINT
identity_column
GENERATE {ALWAYS | BY DEFAULT} AS IDENTITY
   [ ( START WITH constant |
       INCREMENT BY constant |
       MINVALUE constant‭	|‬
       NO MINVALUE |	
       MAXVALUE constant |
       NO MAXVALUE |
       [ NO ] CYCLE ) ]