Create Multiset Temporary Table in Teradata

Volatile table in Teradata is used to store data temporarily in a session. The table will be destroyed when the session ends. The content of the table can be deleted after each journal step depends on the definition of the table and transaction mode. Volatile table will be stored in the login user database.  This article shows you how to create volatile table in a procedure and then perform DML actions (INSERT, DELETE, UPDATE)  against it and finally return the result set dynamically. 

Create a volatile table

The following are some example SQL DDL statements for creating volatile tables with LOG, NO LOG , ON COMMIT DELETE ROWS and ON COMMIT PRESERVER ROWS. Table vt_1 and vt_3 will become empty after each transaction ends; for table vt_1, vt_2 and vt_4, transition journal logs will be created for each journal step (UPDATE, INSERT, DELETE actions).

CREATE VOLATILE TABLE vt_1, LOG (
  column_1 INTEGER,
  column_2 INTEGER)
ON COMMIT DELETE ROWS;
 
CREATE VOLATILE TABLE vt_2, LOG (
  column_1 INTEGER,
  column_2 INTEGER)
ON COMMIT PRESERVE ROWS;
 
CREATE VOLATILE TABLE vt_3, NO LOG (
  column_1 INTEGER,
  column_2 INTEGER)
ON COMMIT DELETE ROWS;
 
CREATE VOLATILE TABLE vt_4, NO LOG (
  column_1 INTEGER,
  column_2 INTEGER)
ON COMMIT PRESERVE ROWS;

Assuming the following statements are run in Teradata transition mode in the same session of the CREATE statements:

INS vt_1(1,2);
INS vt_2(1,2);
INS vt_3(1,2);
INS vt_4(1,2);

And then run the following SELECT statements:

SEL * FROM vt_1;
SEL * FROM vt_2;
SEL * FROM vt_3;
SEL * FROM vt_4;

Only the second and the fourth statement will return results:

REPLACE PROCEDURE TestDb.TestProc()
DYNAMIC RESULT SETS 1
SQL SECURITY OWNER
BEGIN
	DECLARE SQL_CREATE VARCHAR(1000);
	DECLARE SQL_INSERT VARCHAR(1000);
	DECLARE SQL_SEL VARCHAR(1000);
	DECLARE RANDOM_NUM INT; 
	DECLARE TEMP_TABLE_NAME VARCHAR(100);
	DECLARE C1 CURSOR WITH RETURN ONLY FOR S1;
	
	SET RANDOM_NUM = RANDOM(1,100000);
	SET TEMP_TABLE_NAME = 'VT_TEST_' || CAST(RANDOM_NUM AS VARCHAR(20));
	
	SET SQL_CREATE ='
	CREATE MULTISET VOLATILE TABLE ' || TEMP_TABLE_NAME || ' ,LOG (
	  column_1 INTEGER,
	  column_2 INTEGER)
	ON COMMIT PRESERVE ROWS;';
	CALL DBC.SYSEXECSQL(SQL_CREATE);

	SET SQL_INSERT='INSERT INTO ' || TEMP_TABLE_NAME || ' SEL 0 AS column_1, 1 AS column_2 FROM TestDb.FASTLOAD_CSV;';
	EXECUTE IMMEDIATE SQL_INSERT;
	
	SET SQL_SEL='SEL * FROM ' || TEMP_TABLE_NAME;
	
	PREPARE S1 FROM SQL_SEL;
	OPEN C1;
END;

Call the procedure:

CALL TestDb.TestProc();

Result:

There are several things to pay attention to about the above procedure.

Dynamic SQL statement 

This statement declares the procedure will return a dynamic result set. For this case, we also used dynamic cursor for dynamic SQL statements.

DECLARE C1 CURSOR WITH RETURN ONLY FOR S1;

SET SQL_SEL='SEL * FROM ' || TEMP_TABLE_NAME;
	
PREPARE S1 FROM SQL_SEL;
OPEN C1;

In the above code snippet, dynamic cursor C1 is defined with WITH RETURN ONLY so that the results will be returned to the client. 

EXECUTE IMMEDIATE

For executing dynamic INSERT statement, we need to use EXECUTE IMMEDIATE statement. 

EXECUTE IMMEDIATE SQL_INSERT;

If we use SYSEXECSQL statement, it will error out:

CALL DBC.SYSEXECSQL(SQL_INSERT);

The above statement generates the following error:

CALL Failed.  [7689] TESTPROC:Invalid dynamic SQL statement.