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
truncate+multiple+tables
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';
+2Hi 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.
NC
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.sqlHope this helps.
NC
thanks .. Cheers
Sign up
Already have an account? Login
Welcome to the Rocket Forum!
Please log in or register:
Employee Login | Registration Member Login | RegistrationEnter your E-mail address. We'll send you an e-mail with instructions to reset your password.
77 4th Avenue
Waltham, MA 02451 USA
