Vertica client driver: Load data to temp table

sunith

I am using Vertica client driver version 8.1 to save data to temptable using the following query :
COPY TempTableName FROM STDIN DIRECT DELIMITER '|' RECORD TERMINATOR '\r\n' NO ESCAPE NO COMMIT;

while using this statement to add data to temp table I get a error if I have used "|" in the data to saved.

One of the ways to fix this is using ENCLOSED BY paramater like :
COPY TempTableName FROM STDIN DIRECT DELIMITER '|' RECORD TERMINATOR '\r\n' ENCLOSED BY '"' NO ESCAPE NO COMMIT;

But in this case if I used " in the data to be saved it leads to an error. This there are a generic way to handle this and insert all possible characters ('",.;/:?~!@#$%^&*|\/) on keyboard while saving.

Jim_Knicely

Your situation is the reason my friend Maurizio Felici avoids using printable characters (commas, new-lines,…) as Field/Record separators

See: https://www.linkedin.com/pulse/vertica-vsql-tips-tricks-maurizio-felici/

Check the "Exporting data to file (the safe way)" section.

Is it possible to have the data include an "ASCII value of 1 (CTRL-A)" as the field separator and an "ASCII value 2 (CTRL-B)" for the record separator?

It'll be much easier to load!

Thanks!