I am loading data from a flat file (csv) into a Snowflake table using Qlik Replicate tool. In the Snowflake table, I have a column called "load_rec_num" to store something like ROWNUM of Oracle, that is, an auto incrementing numeric value as data is getting loaded.
I tried 3 approaches on "load_rec_num", all from inside Snowflake, but none is working :
- created "load_rec_num" as an identity column
- created a sequence and had column "load_rec_num" default to sequence.nextval : load_rec_num DEFAULT MY_SEQ.nextval
- created "load_rec_num" with the autoincrement keyword : load_rec_num autoincrement start 1 increment 1
None of these worked for the data load operation. Data got loaded into the table (using the Qlik Replicate jobs) but the column value was NULL. However, when I run INSERT sqls on the same table, this column "load_rec_num" got populated with a number, but that is not what I am looking for.
Is there a way (either from inside Snowflake or by some transformation from inside Qlik Replicate task or by using SQLite statements inside Qlik Replicate) whereby the column "load_rec_num" can be automatically loaded with auto-incrementing number when the data load is happening from Qlik Replicate tool ?
You can set the AUTOINCREMENT or IDENTITY default value for a number column while using the COPY command in Snowflake to load data. When loading data into a table using the COPY command, omit the column in the SELECT statement. The statement automatically populates the column.
You will find more information on this along with an example on the following Snowflake Documentation Page : https://docs.snowflake.com/en/user-guide/data-load-transform.html#include-autoincrement-identity-columns-in-loaded-data