Create Multiset Temporary Table in Teradata

access_time 2 months ago visibility43 comment 0

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:

column_1column_2
12

Create volatile in procedure

The following procedure creates a volatile table with random name and and insert records from an existing physical table; the records in the volatile table is returned to client using dynamic cursor. 

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.


info Last modified by Raymond at 2 months ago copyright This page is subject to Site terms.
Like this article?
Share on

Please log in or register to comment.

account_circle Log in person_add Register

Log in with external accounts

Want to publish your article on Kontext?

Learn more

Kontext Column

Created for everyone to publish data, programming and cloud related articles.
Follow three steps to create your columns.


Learn more arrow_forward

More from Kontext

local_offer teradata local_offer C# local_offer .NET

visibility 7993
thumb_up 0
access_time 6 years ago

In this post, I will demonstrate how to connect to Teradata database via .NET Data Provider for Teradata using C#. Install the .NET Data Provider for Teradata from the following link: Teradata Tools and Utilities Windows Installation Package Name it as’' ‘ConsoleApps.Teradata’. Add ...

local_offer teradata local_offer SQL local_offer teradata-functions

visibility 305
thumb_up 0
access_time 8 months ago

Extract sub string from a string is a common operation in data analytics. In Teradata, function SUBSTRING (SUBSTR) and REGEXP_SUBSTR are provided to achieve that. SUBSTR is used to extract string from a specified location while REGEXP_SUBSTR is used to extract string using regular expressions. -- ...

local_offer teradata local_offer SQL

visibility 8
thumb_up 0
access_time 2 months ago

Teradata has no built-in MD5 function thus custom function needs to be implemented for calculating MD5. This article shows you how to do that using the MD5 message digest UDF provided on Teradata Downloads. Permission CREATE FUNCTION is required for creating UDF in Teradata.  Navigate ...

About column

Tutorials and information about Teradata.

rss_feed Subscribe RSS