Search code examples
teradatateradatasql

Save result of Teradata SQL statement

Does anyone know how I can save the output of the “HELP VOLATILE TABLE” statement as a table that I can use in a query later on.

My goal is to save a list of all Volatile Tables that are currently present.

I tried to use the “HELP VOLATILE TABLE” in a CTE, but it doesn’t do the trick. It refuses to run. Any help is useful.

Update: It seems HELP/SHOW statements can only return data to the client. They can’t be used in a query.

It seems it is possible to write an external stored procedure in f.e. Java that FETCHES this data and INSERTS it into a Global Temporary Table.

My question is whether someone knows how to write said external stored procedure in JAVA, and knows how to import it and use it?

Solution

  • For those using SAS, this is easy to do.

    PROC SQL NOPRINT;
CONNECT TO TERADATA ( &connect_string. );
SELECT *
FROM CONNECTION TO TERADATA 
(HELP VOLATILE TABLE); 
DISCONNECT FROM TERADATA;
QUIT;

    For those using Python, the same can be done easily too.

    import os
import teradatasql
import pandas as pd

with teradatasql.connect('{"host":"your_host_name"}', user="", password = "") as connect:
df = pd.read_sql("HELP VOLATILE TABLE", connect)