CODEWITHSUNDEEP
snowflake-cloud-data-platform
Shanoo
Shanoo

Reputation: 1255

COPY INTO Snowflake Table with Extra Columns as a defined value

I've got a table defined in Snowflake as:

table1

COL1 VARCHAR(16777216)
COL2 VARCHAR(16777216)
tablename VARCHAR(16777216)

and a csv file (only has 2 columns) as below:

table1.csv

COL1
COL2

I want to copy all columns from "table1.csv" to "table1" snowflake table, so data for COL1 and COL2 will get copied from csv to a snowflake table and "tablename" column should be "table1" (filename)

My copy into command looks like this:

  copy into table1
  from @snowflake_stage
  file_format = (type = 'CSV' skip_header = 1 FIELD_OPTIONALLY_ENCLOSED_BY = '"' 
  validate_utf8 = FALSE error_on_column_count_mismatch=false)
  on_error = CONTINUE;

Data is getting loaded successfully for COL1 and Col2 from csv to snowflake table but tablename column is NULL. I want tablename column to have value "table1" for all the rows.

Upvotes: 0

Views: 1548

Answers (1)

demircioglu
demircioglu

Reputation: 3455

Snowflake automatically generates metadata columns for files. You can use METADATA$FILENAME.

It will have the full path, you can either store the full path or use a SUBSTRING command to get the filename without the path or the extension

Upvotes: 0

Related Questions