executing multiple quieries

I am new to verica please help with below question

i want to execute multiple queries in vertica from shell scripting. is there any way i can do by just logging once and execute the next queries or statements one after the other
want to write below code in shell
ex connect to db
pset format unaligned
pset footer
select stmt
dml operation
select stmt

i tried using different commands but was not successful. please answer


  • Options
    Maybe something like this?

    [dbadmin@vertica01 ~]$ vsql -Atc "select * from test;insert into test values ('Hi');commit;select * from test;"

  • Options
    vsql -c runs all the SQL but only returns the results from the last query.
    $ vsql -Atc "select 1; select 2; select 3;"  3
    If you want all of the results, use pipes:
    $ echo "select 1; select 2; select 3;" | vsql -At  1  2  3    
    For vsql scripts that want to run lot of queries, I've had some good luck using \o:
    for x in system nodes schemata; do echo -e "\o /tmp/vsql-$x.out\nselect * from $x;\n\o\n"; done | vsql -At
  • Options
    Another option its redirection (almost same as example 1 of Ben Vandiver):
    daniel@synapse:~$ vsql << EOF
    > \pset format unaligned
    > \pset footer
    > select 1;
    > insert into table1 values (4, 'AA', '2015-01-01 12:00:00');
    > commit;
    > select * from table1 where id = 4;
    > \q
    > EOF
    Output format is unaligned.
    Default footer is off.
    4|AA|2015-01-01 12:00:00

    Cheers ;)

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file