Snowflake COPY: how to insert load time automatically when I use the MATCH_BY_COLUMN_NAME option

Question

I use Snowflake copy with MATCH_BY_COLUMN_NAME option. We use MATCH_BY_COLUMN_NAME because we have a very long and evolving list of column names. Is there a way to include a LOAD_TIME field with the current timestamp at the time of loading (or copy compilation)?

I tried to add a LOAD_TIME timestamp default current_timestamp() in the destination table, but I got NULL value after copy because of the MATCH_BY_COLUMN_NAME.

Please help. Thank you.

Answer 1

EDIT:

COPY INTO:

INCLUDE_METADATA = ( column_name = METADATA$field [ , column_name = METADATA$field ... ] )

Definition

A user-defined mapping between a target table’s existing columns to its METADATA$ columns. This copy option can only be used with the MATCH_BY_COLUMN_NAME copy option. The valid input for METADATA$field includes the following:

METADATA$FILENAME
METADATA$FILE_ROW_NUMBER
METADATA$FILE_CONTENT_KEY
METADATA$FILE_LAST_MODIFIED
METADATA$START_SCAN_TIME

COPY INTO table_name 
FROM @stage_name
MATCH_BY_COLUMN_NAME = CASE_INSENSITIVE
INCLUDE_METADATA = (LOAD_TIME = METADATA$START_SCAN_TIME);

---

It is default behaviour, COPY into inserts NULL into other columns and does not allow to use transformation syntax, thus NULL even with default defined.

COPY INTO t
FROM (SELECT CURRENT_TIMESTAMP() AS LOAD_TIME, ...
      FROM @stage)
MATCH_BY_COLUMN_NAME = CASE_INSENSITIVE;
-- SQL compilation error: match_by_column_name is not supported with copy transform

COPY INTO:

MATCH_BY_COLUMN_NAME = CASE_SENSITIVE | CASE_INSENSITIVE | NONE

... Note

• If additional non-matching columns are present in the target table, the COPY operation inserts NULL values into these columns. These columns must support NULL values.

• The COPY statement does not allow specifying a query to further transform the data during the load (i.e. COPY transformation).