When you create a table in Access automatically the first field called is added to it

Categories:

Table, View, & Sequence DDL

Creates a new table in the current/specified schema or replaces an existing table. A table can have multiple columns, with each column definition consisting of a name, data type, and optionally whether the column:

• Requires a value (NOT NULL).

• Has a default value.

• Has any referential integrity constraints (primary key, foreign key, etc.).

In addition, this command supports the following variants:

• CREATE TABLE … AS SELECT (creates a populated table; also referred to as CTAS)

• CREATE TABLE … USING TEMPLATE (creates a table with the column definitions derived from a set of staged files)

• CREATE TABLE … LIKE (creates an empty copy of an existing table)

• CREATE TABLE … CLONE (creates a clone of an existing table)

ALTER TABLE , DROP TABLE , SHOW TABLES , DESCRIBE TABLE

In this Topic:

Syntax¶

CREATE [ OR REPLACE ]
    [ { [ LOCAL | GLOBAL ] TEMP[ORARY] | VOLATILE } | TRANSIENT ]
    TABLE [ IF NOT EXISTS ] 
    (  
                             [ COLLATE '' ]
                                /* COLLATE is supported only for text data types (VARCHAR and synonyms) */
                             [ COMMENT '' ]
                             [ { DEFAULT 
                               | { AUTOINCREMENT | IDENTITY } [ { (  ,  ) | START  INCREMENT  } ] } ]
                                /* AUTOINCREMENT (or IDENTITY) is supported only for numeric data types (NUMBER, INT, FLOAT, etc.) */
                             [ NOT NULL ]
                             [ [ WITH ] MASKING POLICY  [ USING (  ,  , ... ) ] ]
                             [ [ WITH ] TAG (  = '' [ ,  = '' , ... ] ) ]
                             [ inlineConstraint ]
      [ ,   [ ... ] ]
      [ , outoflineConstraint ]
      [ , ... ] )
  [ CLUSTER BY (  [ ,  , ... ] ) ]
  [ STAGE_FILE_FORMAT = ( { FORMAT_NAME = ''
                           | TYPE = { CSV | JSON | AVRO | ORC | PARQUET | XML } [ formatTypeOptions ] } ) ]
  [ STAGE_COPY_OPTIONS = ( copyOptions ) ]
  [ DATA_RETENTION_TIME_IN_DAYS =  ]
  [ MAX_DATA_EXTENSION_TIME_IN_DAYS =  ]
  [ CHANGE_TRACKING = { TRUE | FALSE } ]
  [ DEFAULT_DDL_COLLATION = '' ]
  [ COPY GRANTS ]
  [ [ WITH ] ROW ACCESS POLICY  ON (  [ ,  ... ] ) ]
  [ [ WITH ] TAG (  = '' [ ,  = '' , ... ] ) ]
  [ COMMENT = '' ]

Where:

inlineConstraint ::=
  [ CONSTRAINT  ]
  { UNIQUE | PRIMARY KEY | { [ FOREIGN KEY ] REFERENCES  [ (  ) ] } }
  [  ]

For additional inline constraint details, see CREATE | ALTER TABLE … CONSTRAINT.

outoflineConstraint ::=
  [ CONSTRAINT  ]
  {
     UNIQUE [ (  [ ,  , ... ] ) ]
   | PRIMARY KEY [ (  [ ,  , ... ] ) ]
   | [ FOREIGN KEY ] [ (  [ ,  , ... ] ) ]
                     REFERENCES  [ (  [ ,  , ... ] ) ]
  }
  [  ]

For additional out-of-line constraint details, see CREATE | ALTER TABLE … CONSTRAINT.

formatTypeOptions ::=
-- If TYPE = CSV
     COMPRESSION = AUTO | GZIP | BZ2 | BROTLI | ZSTD | DEFLATE | RAW_DEFLATE | NONE
     RECORD_DELIMITER = '' | NONE
     FIELD_DELIMITER = '' | NONE
     FILE_EXTENSION = ''
     SKIP_HEADER = 
     SKIP_BLANK_LINES = TRUE | FALSE
     DATE_FORMAT = '' | AUTO
     TIME_FORMAT = '' | AUTO
     TIMESTAMP_FORMAT = '' | AUTO
     BINARY_FORMAT = HEX | BASE64 | UTF8
     ESCAPE = '' | NONE
     ESCAPE_UNENCLOSED_FIELD = '' | NONE
     TRIM_SPACE = TRUE | FALSE
     FIELD_OPTIONALLY_ENCLOSED_BY = '' | NONE
     NULL_IF = ( '' [ , '' ... ] )
     ERROR_ON_COLUMN_COUNT_MISMATCH = TRUE | FALSE
     REPLACE_INVALID_CHARACTERS = TRUE | FALSE
     EMPTY_FIELD_AS_NULL = TRUE | FALSE
     SKIP_BYTE_ORDER_MARK = TRUE | FALSE
     ENCODING = '' | UTF8
-- If TYPE = JSON
     COMPRESSION = AUTO | GZIP | BZ2 | BROTLI | ZSTD | DEFLATE | RAW_DEFLATE | NONE
     DATE_FORMAT = '' | AUTO
     TIME_FORMAT = '' | AUTO
     TIMESTAMP_FORMAT = '' | AUTO
     BINARY_FORMAT = HEX | BASE64 | UTF8
     TRIM_SPACE = TRUE | FALSE
     NULL_IF = ( '' [ , '' ... ] )
     FILE_EXTENSION = ''
     ENABLE_OCTAL = TRUE | FALSE
     ALLOW_DUPLICATE = TRUE | FALSE
     STRIP_OUTER_ARRAY = TRUE | FALSE
     STRIP_NULL_VALUES = TRUE | FALSE
     REPLACE_INVALID_CHARACTERS = TRUE | FALSE
     IGNORE_UTF8_ERRORS = TRUE | FALSE
     SKIP_BYTE_ORDER_MARK = TRUE | FALSE
-- If TYPE = AVRO
     COMPRESSION = AUTO | GZIP | BROTLI | ZSTD | DEFLATE | RAW_DEFLATE | NONE
     TRIM_SPACE = TRUE | FALSE
     NULL_IF = ( '' [ , '' ... ] )
-- If TYPE = ORC
     TRIM_SPACE = TRUE | FALSE
     NULL_IF = ( '' [ , '' ... ] )
-- If TYPE = PARQUET
     COMPRESSION = AUTO | LZO | SNAPPY | NONE
     SNAPPY_COMPRESSION = TRUE | FALSE
     BINARY_AS_TEXT = TRUE | FALSE
     TRIM_SPACE = TRUE | FALSE
     NULL_IF = ( '' [ , '' ... ] )
-- If TYPE = XML
     COMPRESSION = AUTO | GZIP | BZ2 | BROTLI | ZSTD | DEFLATE | RAW_DEFLATE | NONE
     IGNORE_UTF8_ERRORS = TRUE | FALSE
     PRESERVE_SPACE = TRUE | FALSE
     STRIP_OUTER_ELEMENT = TRUE | FALSE
     DISABLE_SNOWFLAKE_DATA = TRUE | FALSE
     DISABLE_AUTO_CONVERT = TRUE | FALSE
     SKIP_BYTE_ORDER_MARK = TRUE | FALSE

copyOptions ::=
     ON_ERROR = { CONTINUE | SKIP_FILE | SKIP_FILE_ | 'SKIP_FILE_%' | ABORT_STATEMENT }
     SIZE_LIMIT = 
     PURGE = TRUE | FALSE
     RETURN_FAILED_ONLY = TRUE | FALSE
     MATCH_BY_COLUMN_NAME = CASE_SENSITIVE | CASE_INSENSITIVE | NONE
     ENFORCE_LENGTH = TRUE | FALSE
     TRUNCATECOLUMNS = TRUE | FALSE
     FORCE = TRUE | FALSE

Variant Syntax¶

CREATE TABLE … AS SELECT (also referred to as CTAS)¶

Creates a new table populated with the data returned by a query:

CREATE [ OR REPLACE ] TABLE  [ (  [  ] ,  [  ] , ... ) ]
  [ CLUSTER BY (  [ ,  , ... ] ) ]
  [ COPY GRANTS ]
  AS SELECT 
  [ ... ]

A masking policy can be applied to a column in a CTAS statement. Specify the masking policy after the column data type. Similarly, a row access policy can be applied to the table. For example:

CREATE TABLE  (   [ with ] masking policy  [ , ... ] )
  ...
  [ WITH ] ROW ACCESS POLICY  ON (  [ , ... ] )
  AS SELECT 
  [ ... ]

Note

In a CTAS, the COPY GRANTS clause is valid only when combined with the OR REPLACE clause. COPY GRANTS copies permissions from the table being replaced with CREATE OR REPLACE (if it already exists), not from the source table(s) being queried in the SELECT statement. CTAS with COPY GRANTS allows you to overwrite a table with a new set of data while keeping existing grants on that table.

For more details about COPY GRANTS, see COPY GRANTS in this document.

CREATE TABLE … USING TEMPLATE¶

Creates a new table with the column definitions derived from a set of staged files containing semi-structured data. This feature is currently limited to Apache Parquet, Apache Avro, and ORC files.

CREATE [ OR REPLACE ] TABLE 
  [ COPY GRANTS ]
  USING TEMPLATE 
  [ ... ]

Note

If the statement is replacing an existing table of the same name, then the grants are copied from the table being replaced. If there is no existing table of that name, then the grants are copied from the source table being cloned.

For more details about COPY GRANTS, see COPY GRANTS in this document.

CREATE TABLE … LIKE¶

Creates a new table with the same column definitions as an existing table, but without copying data from the existing table. Column names, types, defaults, and constraints are copied to the new table:

CREATE [ OR REPLACE ] TABLE  LIKE 
  [ CLUSTER BY (  [ ,  , ... ] ) ]
  [ COPY GRANTS ]
  [ ... ]

For more details about COPY GRANTS, see COPY GRANTS in this document.

Note

CREATE TABLE … LIKE for a table with an auto-increment sequence accessed through a data share is currently not supported.

CREATE TABLE … CLONE¶

Creates a new table with the same column definitions and containing all the existing data from the source table, without actually copying the data. This variant can also be used to clone a table at a specific time/point in the past (using Time Travel):

CREATE [ OR REPLACE ] TABLE  CLONE 
  [ { AT | BEFORE } ( { TIMESTAMP =>  | OFFSET =>  | STATEMENT =>  } ) ]
  [ COPY GRANTS ]
  [ ... ]

Note

If the statement is replacing an existing table of the same name, then the grants are copied from the table being replaced. If there is no existing table of that name, then the grants are copied from the source table being cloned.

For more details about COPY GRANTS, see COPY GRANTS in this document.

For more details about cloning, see CREATE