-1

I am trying to get an insert statement export from a vertica database table. I am trying to create a query that generates insert statements but I am experiencing syntax issues. How can I do something like the below in Vertica?

select "insert into owner.target_tbl values ( "  ||"'"||a.Head_id||"',"||"'"||a.creation_dt||"',"||"'"||a.section"'"||");" as Query_column
from source_tbl a
Doublespeed
  • 1,153
  • 4
  • 16
  • 29
  • Is the problem that the insert statements that are generated don't work, or that the generation of the statements themselves doesn't work? What are you inserting to from the vertica export? You have the netezza tag, if it's NZ, you shouldn't be inserting row-by-row. – N West Mar 25 '13 at 20:26
  • 1
    I'm absolutely certain that "*I amexperiencing syntax issues*" is **not** a valid error message in Vertica. Please post the exact error message and the SQL statement that is the result of your query. –  Mar 26 '13 at 09:14

3 Answers3

4

If you quote a name with double-quotes, Vertica considers that it is a column, which in your case will lead to an error.

The solution will be to:

  • Use single quotes around your strings

  • If you need a single quote inside your single-quoted string, just double it (twice the single quote, not a double quote):

    select 'insert into owner.target_tbl values (' ||'''' || 'a string' ||''', ' || 2 ||');' as Query_column;

                     Query_column                     
------------------------------------------------------
insert into owner.target_tbl values ('a string', 2);
(1 row)
Guillaume
  • 61
  • 2
3

Use single quotes not double quotes:

=> select "foo" || "bar" from dual;
ERROR:  column "foo" does not exist
=> select 'foo' || 'bar' from dual;
 ?column? 
----------
 foobar
(1 row)
Sharon
  • 61
  • 1
1

Advice: make sure you add cases to wrap up possible null values- if one of the arguments to concatenation sequence is null- your result will be null.

kirylm
  • 101
  • 1
  • 1
  • 8