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?

Answer 3

Create the target table

create or replace table mytable (
  col1 number autoincrement start 1 increment 1,
  col2 varchar,
  col3 varchar
  );

Stage a data file in the internal user stage

put file:///tmp/myfile.csv @~;

Query the staged data file

select $1, $2 from @~/myfile.csv.gz t;

+-----+-----+
| $1  | $2  |
|-----+-----|
| abc | def |
| ghi | jkl |
| mno | pqr |
| stu | vwx |
+-----+-----+

Omit the sequence column in the COPY statement

copy into mytable (col2, col3)
from (
  select $1, $2
  from @~/myfile.csv.gz t
)
;

select * from mytable;

+------+------+------+
| COL1 | COL2 | COL3 |
|------+------+------|
|    1 | abc  | def  |
|    2 | ghi  | jkl  |
|    3 | mno  | pqr  |
|    4 | stu  | vwx  |
+------+------+------+