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

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).