AUTOINCREMENT primary key for snowflake bulk loading

Question

I would like to upload data into snowflake table. The snowflake table has a primary key field with AUTOINCREMENT.

When I tried to upload data into snowflake without a primary key field, I've received following error message:

The COPY failed with error: Number of columns in file (2) does not match that of the corresponding table (3), use file format option error_on_column_count_mismatch=false to ignore this error

Does anyone know if I can bulk load data into a table that has an AUTOINCREMENT primary key?

knozawa

Answer 1

You can query the stage file using file format to load your data. I have created sample table like below. First column set autoincrement:

-- Create the target table
create or replace table Employee (
  empidnumber autoincrement start 1 increment 1,
  name varchar,
  salary varchar
  );

I have staged one sample file into snowflake internal stage to load data into table and I have queried stage file using following and then I have executed following copy cmd:

copy into mytable (name, salary )from (select $1, $2 from @test/test.csv.gz                                );

And it loaded the table with incremented values.

Answer 2

The docs have the following example which suggests this can be done: https://docs.snowflake.net/manuals/user-guide/data-load-transform.html#include-autoincrement-identity-columns-in-loaded-data

-- Omit the sequence column in the COPY statement
copy into mytable (col2, col3)
from (
  select $1, $2
  from @~/myfile.csv.gz t
)
;

Could you please try this syntax and see if it works for you?