CREATE GLOBAL TEMPORARY TABLE in Teradata

About GLOBAL TEMPORARY:

You are working in a Teradata database application.
You have to CREATE a table and insert some records for performance analysis.
The table has several columns and you have to DELETE the content once you have done with your job.
But you don't want to drop the table since it is being used daily for the analysis.

What can be done for this?

Teradata gives GLOBAL TEMPORARY option with CREATE TABLE syntax.

What special ?

If you create a table with GLOBAL TEMPORARY option, the life of the CONTENTS of the table will be only for the current session.
They will be automatically deleted by Teradata manager once session expires. (Only the content not definition)

Syntax:

CREATE GLOBAL TEMPORARY TABLE table_1
(
column1 datatype,
column2 datatype,
.
.
columnN datatype
);

CREATE GLOBAL TEMPORARY TABLE employee
(
ID INT,
Name VARCHAR(20)
);

Interesting about GLOBAL TEMPORARY:

Now, you are creating a GLOBAL TEMPORARY table and inserting some records successfully into it.
SELECT * from the GLOBAL TEMPORARY table. Shocked ? ...
Yes. You cannot see the records because Teradata becomes strict to GLOBAL TEMPORARY tables by incorporating ON COMMIT DELETE ROWS by default to add more meaning to TEMPORARY.

so, if you want to see the records, you have to explicitly write ON COMMIT PRESERVE ROWS immediately after CREATE TABLE syntax.

CREATE GLOBAL TEMPORARY TABLE table_1
(
column1 datatype,
column2 datatype,
.
.
columnN datatype
) ON COMMIT PRESERVE ROWS;

More about GLOBAL TEMPORARY:

1. You can have 2000 volatile tables in an active session.
2. REFERENTIAL constraints are not allowed.



Here GLOBAL TEMPORARY says, DO and GO.. i will PRESERVE and take care...

..