How to bulk insert Array in snowflake?

Question

I am ingesting data in snowflake using the following command:

 copy into ArrayTable from (select $1, array_construct($2),array_construct($3) 

 from @ArrayTable_stage/ArrayTable-1-1.csv.gz) 
    
 file_format = (TYPE=CSV FIELD_DELIMITER='|' FIELD_OPTIONALLY_ENCLOSED_BY='\"')

But the array_construct() is not feasible for insertion of multiple tables since I have to manually specify columns which are of ARRAY datatype.

What is the alternative method for bulk insertion of Arrays in snowflake?

Answer 1

I am not sure if this article would help or you have already seen it: https://community.snowflake.com/s/article/How-to-use-an-INSERT-statement-to-add-an-ARRAY-column-to-the-table

I am also following your question. Just to see what you are trying is achievable in snowflake dynamically or we always have to do it manually.

Answer 2

Could you share some sample data of what your source/stage looks like.

But, creating the table with the columns as Variant datatype should work for what you are looking to do. You then should not need to do the array_construct()

https://docs.snowflake.com/en/sql-reference/data-types-semistructured.html