The temporary tables can be created at runtime and can be used for the same operations as the normal tables. But, based on the table types, the scope is limited.

• Temporary Tables lets you store and process intermediate results.
• Temporary tables only last as long as the session is alive.
• Data in temporary tables is automatically deleted at the end of the database session, even if it ends abnormally.

SAP HANA supports 2 types of temporary tables:

• Global Temporary Table
• Local Temporary Table

Global Temporary Table

• Table definition is globally available to any connection once created. Means metadata of the table is shared across sessions.
• The data in a global temporary table is session-specific meaning that data inserted by a session can only be accessed by that session.
• The table is dropped when the last connection using it is closed.

CREATE GLOBAL TEMPORARY TABLE my_global_temp_table (
            Column1 INTEGER,
            Column2 VARCHAR(10),
            Column3 VARCHAR(20)
);

Local Temporary Table

• Temporary table is visible only in the current session.
• Data in a local temporary table is session-specific meaning only the owner session of the local temporary table is allowed to insert/read/truncate the data.
• It exists for the duration of the session and data from the local temporary table is automatically dropped when the session is terminated.
• Local temporary table name is stared with hash (“#”) sign.

CREATE LOCAL TEMPORARY TABLE #my_local_temp_table (
            Column1 INTEGER,
            Column2 VARCHAR(10),
            Column3 VARCHAR(20)
);

Examples

It is always good to also drop the table when not in use. Otherwise the following error can occur when running the procedure for the second time without ending the session: SAP DBTech JDBC: [288]: cannot use duplicate table name: search table error: …

How-To drop the temporary table

CREATE PROCEDURE procedure_name
(...) language sqlscript
sql security invoker as
BEGIN

  CREATE LOCAL TEMPORARY TABLE #table_name("LINE1" nclob, "LINE2" nclob);
  DROP TABLE #table_name;

END;

How-To fill the temporary table from another table

CREATE PROCEDURE procedure_name
(...) language sqlscript
sql security invoker as
BEGIN

  CREATE LOCAL TEMPORARY TABLE #table_name("Line1" nclob, "Line2" nclob);
  
  insert into #table_name
    SELECT "Line1", "Line2"
    FROM another_table
    WHERE field = 'value';
  
  DROP TABLE #table_name;

END;	

Authorization

If necessary the authorization can be granted:

GRANT CREATE TEMPORARY TABLE ON SCHEMA "<schema name>" TO <user>;

Links

I have taken an inspiration from the following page: SAP HANA Temporary Tables