Shape the data into a single string so that it can be passed into VALUES
Insert them into a Snowflake database; INSERT INTO ... VALUES (), (), ()
What makes things complicated is that some of the text fields in the source tables have all possible types of characters, including the single quote ' as part of a string. E.g.
field1 || field2 || field5
1 || Spot ||Let's go out tonight
In order insert them properly, during point 1 I already make provisions to escape such fields based on Snowflake's documentation, see here.
However this seems to jam when there actually is a single quote in the string, and this does not seem to be a Snowflake issue because by doing it in the console on hardcoded values works fine. So it must be that, for some reason the combination of the double dollar sign $$ + a single quote throws off QV.
Here a picture from the log when the script crashes. Note how, for some reason, single quotes appear to double.
And here the script (some fields are anonomised)
ODBC CONNECT TO Postgres;
--// 2 fields in total
--// '''' is PG's way of escaping the single quote
'(' || '''' || ci1_group || '''' || ',' || ci1_id || ',' || '$$ ' || ci1_info || ' $$' || ',' || '''' || ci1_source || '''' || ',' || COALESCE('$$' || ci1_label || '$$','NULL') || ')' AS single_string
,ROW_NUMBER() OVER() AS rn1
FOR i=1 TO NoOfRows('PgTable') // about 1400
LOAD single_string AS ss RESIDENT PgTable WHERE rn1 = $(i);
LET vValues = PEEK('ss');
DROP TABLES tempt;
ODBC CONNECT TO Snowflake;
INSERT INTO "database"."schema".SF_table_1 (ci1_group, ci1_id, ci1_info, ci1_source, ci1_label)