truncate+multiple+tables

kranthi_1

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

Marco_Gessner

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_C

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

kranthi_1

thanks .. Cheers