Temporary Table Support for Teradata

For general information about this feature, see Temporary Table Support for SAS/ACCESS .

When you specify CONNECTION=GLOBAL, you can reference a temporary table throughout a SAS session, in both DATA steps and procedures. Due to a Teradata limitation, FastLoad and FastExport do not support use of temporary tables at this time.

Teradata supports two types of temporary tables, global and volatile. With the use of global temporary tables, the rows are deleted after the connection is closed but the table definition itself remains. With volatile temporary tables, the table (and all rows) are dropped when the connection is closed.

When accessing a volatile table with a LIBNAME statement, it is recommended that you do not use these options:

If you use DATABASE= or SCHEMA=, you must specify DBMSTEMP=YES in the LIBNAME statement. This denotes that all tables accessed through it and all tables that it creates are volatile tables.

DBMSTEMP= also causes all table names to be not fully qualified for either SCHEMA= or DATABASE=. In this case, you should use the LIBNAME statement only to access tables—either permanent or volatile—within your default database or schema.

You can drop a temporary table at any time, or allow it to be implicitly dropped when the connection is terminated. Temporary tables do not persist beyond the scope of a single connection.

This example shows how to use a temporary table.

/* Set global connection for all tables. */
libname x teradata user=test pw=test server=boom connection=global;

/* Create global temporary table & store in the current database schema. */
proc sql;
   connect to teradata(user=test pw=test server=boom connection=global);
   execute (CREATE GLOBAL TEMPORARY TABLE temp1 (col1 INT )
            ON COMMIT PRESERVE ROWS) by teradata;
   execute (COMMIT WORK) by teradata;
quit;

/* Insert 1 row into the temporary table to specify the table. */
proc sql;
   connect to teradata(user=test pw=test server=boom connection=global);
   execute (INSERT INTO temp1 VALUES(1)) by teradata;
   execute (COMMIT WORK) by teradata;
quit;

/* Access the temporary table through the global libref. */
data work.new_temp1;
set x.temp1;
run;

/* Access the temporary table through the global connection. */
proc sql;
   connect to teradata (user=test pw=test server=boom connection=global);
   select * from connection to teradata (select * from temp1);
quit;

/* Drop the temporary table. */
proc sql;
   connect to teradata(user=prboni pw=prboni server=boom connection=global);
   execute (DROP TABLE temp1) by teradata;
   execute (COMMIT WORK) by teradata;
quit;

This is an example of how to use a volatile table.

/* Set global connection for all tables. */
libname x teradata user=test pw=test server=boom connection=global;

/* Create a volatile table. */
proc sql;
   connect to teradata(user=test pw=test server=boom connection=global);
   execute (CREATE VOLATILE TABLE temp1 (col1 INT)
            ON COMMIT PRESERVE ROWS) by teradata;
   execute (COMMIT WORK) by teradata;
quit;

/* Insert 1 row into the volatile table. */
proc sql;
   connect to teradata(user=test pw=test server=boom connection=global);
   execute (INSERT INTO temp1 VALUES(1)) by teradata;
   execute (COMMIT WORK) by teradata;
quit;

/* Access the temporary table through the global libref. */
data _null_;
   set x.temp1;
   put _all_;
run;

/* Access the volatile table through the global connection. */
proc sql;
   connect to teradata (user=test pw=test server=boom connection=global);
   select * from connection to teradata (select * from temp1);
quit;

/* Drop the connection & the volatile table is automatically dropped. */
libname x clear;

/* To confirm that it is gone, try to access it. */
libname x teradata user=test pw=test server=boom connection=global;

/* It is not there. */
proc print data=x.temp1;
run;