We're Moving!

The Vertica Forum is moving to a new OpenText Analytics Database (Vertica) Community.

Join us there to post discussion topics, learn about

product releases, share tips, access the blog, and much more.

Create My New Community Account Now


truncate+multiple+tables — Vertica Forum

truncate+multiple+tables

how to truncate multiple tables in vertica assume 300+ tables

below is the sql code

sqlplus -s user password
declare
 cursor c1 is select distinct(tname) from tab
    begin
        for i in c1
        loop
        begin
            execute immediate 'TRUNCATE TABLE ' || i.TNAME;
    end




Comments

  • This is basic SQL generating SQL stuff, Kranthi:

    Here's a script that you run with vsql - with these environment variables set previously:

    VSQL_DATABASE, VSQL_HOST, VSQL_PASSWORD, VSQL_USER

    It counts the rows in each table in my 'ofsdata' schema. Happy Playing - marco:


    -- remove column titles in vsql
    \pset format unaligned
    -- remove the (n rows) confirmation after a report in vsql
    \pset footer          
    -- redirect the report's output to be piped into yet another vsql session
    \o | vsql             
    select
     'select '''||table_name||''', count(*) from '||table_schema||'.'||table_name||';'
      as "--the_script"
    from tables where table_schema='ofsdata';


  • Navin_CNavin_C Vertica Customer
    Hi Kranthi,

    The statements referred by you are not available with Vertica.
    Basically programmable SQL is not yet supported by Vertica, but there are workarounds for everything :)

    So suppose you want to truncate 300 tables at once.
    You can generate the SQL for 300 tables this way.
    \pset format unaligned 
    \pset footer
    select 'Truncate table '|| table_schema|| '.' || table_name ||  ';' from tables limit 300;  
    You can redirect the output to  sql file and run sql file using
    vsql -f  filename.sql  
    Hope this helps.
    NC
  • thanks .. Cheers

Leave a Comment

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