Skip to main content

Copy into Table

COPY INTO table copy options parameters

FORMAT_NAME and TYPE are mutually exclusive and cannot specify both in the same copy_statement configuration.

The COPY INTO parameter supports the following parameters.

  • See Unsupported COPY INTO table copy_options parameters for Pipe
  • INTO:
    • REQUIRED
    • Configuration Key: into
      • DATABASE: Name of the database, in which the table exists
        • REQUIRED if Table is present in another Database than the current one
        • Configuration Key: database
        • Data Type: String
      • SCHEMA: Name of the schema, in which the table exists
        • REQUIRED if Table is present in another Database or another Schema than the current one
        • Configuration Key: schema
        • Data Type: String
      • TABLE: Name of the table
        • REQUIRED
        • Configuration Key: table
        • Data Type: String
  • FROM:
    • REQUIRED
    • Configuration Key: from
      • DATABASE: Name of the database, in which the stage exists
        • REQUIRED if Stage is present in another Database or another Schema than the current one
        • Configuration Key: database
        • Data Type: String
      • SCHEMA: Name of the schema, in which the stage exists
        • REQUIRED if Stage is present in another Database than the current one
        • Configuration Key: schema
        • Data Type: String
      • STAGE: Name the External stage.
        • REQUIRED
        • Configuration Key: stage
  • FILE_FORMAT
    • Configuration Key: file_format . See here for definition of File Format
  • PATTERN:
    • Configuration Key: pattern
    • Data Type: <regex_pattern>
  • ENFORCE_LENGTH:
    • Configuration Key: enforce_length
    • Data Type: Boolean
  • TRUNCATECOLUMNS
    • Configuration Key: truncate_columns
    • Data Type: Boolean
      • Data Type: Boolean
  • FORCE
    • Configuration Key: force
    • Data Type: Boolean
  • PURGE
    • Configuration Key: purge
    • Data Type: Boolean
  • SIZE_LIMIT
    • Configuration Key: size_limit
    • Data Type: Integer
  • RETURN_FAILED_ONLY
    • Configuration Key: return_failed_only
    • Data Type: Boolean
  • MATCH_BY_COLUMN_NAME
    • Configuration Key: match_by_column_name
    • Data Type: String
    • Possible Values:
      • CASE_SENSITIVE
      • CASE_INSENSITIVE
      • NONE
  • ON_ERROR
    • Configuration Key: on_error
    • Data Type: String

File Format

  • FILE_FORMAT
    • Configuration Key: file_format
      • FORMAT_DATABASE: Name of the format database, in which the file format exists
        • REQUIRED if format name is present in another Database than the current one
        • Configuration Key: format_database
        • Data Type: String
      • FORMAT_SCHEMA: Name of the format schema, in which the file format exists
        • REQUIRED if format_database is present
        • Configuration Key: format_schema
        • Data Type: String
      • FORMAT_NAME: Name of the file format
        • REQUIRED if format_database and format_schema is present
        • Configuration Key: format_name
        • Data Type: String
      • TYPE: Specifies the format of the data files to load.
        • REQUIRED if format_database, format_schema and format_name are not present.
        • Configuration Key: type
        • Data Type: String
        • Possible Values:
          • CSV
          • JSON
      • COMPRESSION:
        • Configuration Key: compression
        • Data Type: String/null
        • Possible Values:
          • AUTO
          • GZIP
          • BZ2
          • BROTLI
          • ZSTD
          • DEFLATE
          • RAW_DEFLATE
          • NONE
      • RECORD_DELIMITER:
        • Configuration Key: record_delimiter
        • Data Type: String/null
      • FIELD_DELIMITER:
        • Configuration Key: field_delimiter
        • Data Type: String/null
      • TIME_FORMAT:
        • Configuration Key: time_format
        • Data Type: String
      • DATE_FORMAT:
        • Configuration Key: date_format
        • Data Type: String
      • TIMESTAMP_FORMAT:
        • Configuration Key: timestamp_format
        • Data Type: String
      • SKIP_BLANK_LINES:
        • Configuration Key: skip_blank_lines
        • Data Type: Boolean
      • SKIP_HEADER:
        • Configuration Key: skip_header
        • Data Type: Integer
      • BINARY_FORMAT:
        • Configuration Key: binary_format
        • Data Type: String
        • Possible Values:
          • HEX
          • BASE64
          • UTF8
      • ESCAPE:
        • Configuration Key: escape
        • Data Type: String/null
      • ESCAPE_UNENCLOSED_FIELD:
        • Configuration Key: escape_unenclosed_field
        • Data Type: String/null
      • TRIM_SPACE:
        • Configuration Key: trim_space
        • Data Type: Boolean
      • FIELD_OPTIONALLY_ENCLOSED_BY:
        • Configuration Key: field_optionally_enclosed_by
        • Data Type: String/null
      • NULL_IF:
        • Configuration Key: null_if
        • Data Type: String
      • ERROR_ON_COLUMN_COUNT_MISMATCH:
        • Configuration Key: error_on_column_count_mismatch
        • Data Type: Boolean
      • REPLACE_INVALID_CHARACTERS:
        • Configuration Key: replace_invalid_characters
        • Data Type: Boolean
      • VALIDATE_UTF8:
        • Configuration Key: validate_utf8
        • Data Type: Boolean
      • EMPTY_FIELD_AS_NULL:
        • Configuration Key: empty_field_as_null
        • Data Type: Boolean
      • SKIP_BYTE_ORDER_MARK:
        • Configuration Key: skip_byte_order_mark
        • Data Type: Boolean
      • ENCODING:
        • Configuration Key: encoding
        • Data Type: String
      • ENABLE_OCTAL:
        • Configuration Key: enable_octal
        • Data Type: Boolean
      • ALLOW_DUPLICATE:
        • Configuration Key: allow_duplicate
        • Data Type: Boolean
      • STRIP_OUTER_ARRAY:
        • Configuration Key: strip_outer_array
        • Data Type: Boolean
      • STRIP_NULL_VALUES:
        • Configuration Key: strip_null_values
        • Data Type: Boolean
      • IGNORE_UTF8_ERRORS:
        • Configuration Key: ignore_utf8_errors
        • Data Type: Boolean
      • BINARY_AS_TEXT:
        • Configuration Key: binary_as_text
        • Data Type: Boolean
      • PRESERVE_SPACE:
        • Configuration Key: preserve_space
        • Data Type: Boolean
      • STRIP_OUTER_ELEMENT:
        • Configuration Key: strip_outer_element
        • Data Type: Boolean
      • DISABLE_SNOWFLAKE_DATA:
        • Configuration Key: disable_snowflake_data
        • Data Type: Boolean
      • DISABLE_AUTO_CONVERT:
        • Configuration Key: disable_auto_convert
        • Data Type: Boolean

Unsupported copy_options parameters for Pipe

All COPY INTO table copy options are supported except for the following:

  • FILES
  • ON_ERROR
  • SIZE_LIMIT
  • PURGE
  • FORCE
  • RETURN_FAILED_ONLY
  • VALIDATION_MODE
  • LOAD_UNCERTAIN_FILES