Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am building a small script to:
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;
PgTable:
SQL
--// 2 fields in total
--// '''' is PG's way of escaping the single quote
SELECT
'(' || '''' || ci1_group || '''' || ',' || ci1_id || ',' || '$$ ' || ci1_info || ' $$' || ',' || '''' || ci1_source || '''' || ',' || COALESCE('$$' || ci1_label || '$$','NULL') || ')' AS single_string
,ROW_NUMBER() OVER() AS rn1
FROM schema.table_1
;
FOR i=1 TO NoOfRows('PgTable') // about 1400
tempt:
LOAD single_string AS ss RESIDENT PgTable WHERE rn1 = $(i);
LET vValues = PEEK('ss');
DROP TABLES tempt;
TRACE $(i);
TRACE $(vValues);
ODBC CONNECT TO Snowflake;
SQL
INSERT INTO "database"."schema".SF_table_1 (ci1_group, ci1_id, ci1_info, ci1_source, ci1_label)
VALUES $(vValues)
;
DISCONNECT;
NEXT
EXIT SCRIPT