SapHanaTutorial.Com HOME     Learning-Materials Interview-Q&A Certifications Quiz Online-Courses Forum Jobs Trendz FAQs  
     Explore The World of Hana With Us     
About Us
Contact Us
 Apps
X
HANA App
>>>
Hadoop App
>>>
Tutorial App on SAP HANA
This app is an All-In-One package to provide everything to HANA Lovers.

It contains
1. Courses on SAP HANA - Basics, Modeling and Administration
2. Multiple Quizzes on Overview, Modelling, Architeture, and Administration
3. Most popular articles on SAP HANA
4. Series of Interview questions to brushup your HANA skills
Tutorial App on Hadoop
This app is an All-In-One package to provide everything to Hadoop Lovers.

It contains
1. Courses on Hadoop - Basics and Advanced
2. Multiple Quizzes on Basics, MapReduce and HDFS
3. Most popular articles on Hadoop
4. Series of Interview questions to brushup your skills
Apps
HANA App
Hadoop App
';
Search
Stay Connected
Search Topics
Topic Index
+
-
SQLScript
+
-
Tables in SAP HANA
+
-
Procedure
+
-
Examples of Procedure
SAP HANA Temporary Tables


Temporary Table:

The concept of temporary table helps the developer in a great way. These tables can be created at runtime and can do the all kinds of operations that one normal table can do. 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.
Syntax:
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.
Syntax:
CREATE LOCAL TEMPORARY TABLE #my_local_temp_table (
            Column1 INTEGER,
            Column2 VARCHAR(10),
            Column3 VARCHAR(20)
);



Example 1: Temporary tables in SQL Editor.

This example just shows the use and scope details of temporary tables.
In real time scenario we rarely use temporary tables directly in SQL editor. They are mainly used inside procedures.
    • Open SQL editor and execute following queries.

--Create local temporary table
CREATE LOCAL TEMPORARY TABLE #MY_LOCAL_TEMP_TABLE( ID INTEGER, NAME VARCHAR(10));

--Create global temporary table
CREATE GLOBAL TEMPORARY TABLE MY_GLOBAL_TEMP_TABLE( ID INTEGER, NAME VARCHAR(10));

-- Insert few records in local temporary table
INSERT INTO #MY_LOCAL_TEMP_TABLE VALUES (1,'A');
INSERT INTO #MY_LOCAL_TEMP_TABLE VALUES (2,'B');

-- Insert few records in global temporary table
INSERT INTO MY_GLOBAL_TEMP_TABLE VALUES (3,'C');
INSERT INTO MY_GLOBAL_TEMP_TABLE VALUES (4,'D');

  • This will create one local temporary table and one global temporary table.
    Now run the below queries in the same SQL Editor.

  • SELECT ID, NAME FROM #MY_LOCAL_TEMP_TABLE;
    SELECT ID, NAME FROM MY_GLOBAL_TEMP_TABLE;

  • You will see the output of both tables as:



  • Now open another SQL Editor. Note that new SQL Editor means "a new session".
    Run below queries again here.

  • SELECT ID, NAME FROM #MY_LOCAL_TEMP_TABLE;
    SELECT ID, NAME FROM MY_GLOBAL_TEMP_TABLE;
  • For local temporary table you will get an error like:
    "Could not find table/view #MY_LOCAL_TEMP_TABLE"
  • For global temporary table query will work but output will not contain any data.
  • Conclusion:
      • For global temporary table table definition is globally available to any connection once created. But local temporary table is available only to current session.
      • The data in a global temporary table is session-specific meaning that data inserted by a session can only be accessed by that session.

    Example 2: Use of temporary tables in stored procedure.

    Replace <SCHEMA_NAME> with you schema.
    -- create a table type to be used in procedure
    CREATE TYPE <SCHEMA_NAME>."TT_OUTPUT" AS TABLE (
              ID INTEGER,
              NAME VARCHAR (10)
    );

    CREATE PROCEDURE <SCHEMA_NAME >."DEMO_PROC"(
             IN    ID    INTEGER,
             OUT    OUTPUT_TABLE    <SCHEMA_NAME >."TT_OUTPUT" )
    LANGUAGE SQLSCRIPT SQL SECURITY DEFINER AS
    /*********BEGIN PROCEDURE SCRIPT ************/
    BEGIN
    DECLARE
             NEW_ID INTEGER;
    CREATE LOCAL TEMPORARY TABLE "#TEST_TABLE" (ID INTEGER, NAME VARCHAR(10));

    INSERT INTO "#TEST_TABLE" VALUES(:ID,'A');
    NEW_ID := :ID + 1;
    INSERT INTO "#TEST_TABLE" VALUES(:NEW_ID,'B');
    OUTPUT_TABLE = SELECT ID, NAME FROM "#TEST_TABLE";
    DROP TABLE "#TEST_TABLE";
    END;

    CALL <SCHEMA_NAME >."DEMO_PROC" (1, ?);


    To know more about Procedure, check the articles:




    Support us by sharing this article.



    Explore More
    Close X
    Close X

    5 thoughts on “SAP HANA Temporary Tables

    1. Sergey says:

      >Global Temporary Table:
      >The table is dropped when the last connection using it is closed.

      It’s wrong. SAP HANA Reference SQL: “Data in a global temporary column table is session-specific meaning only the owner session of the global temporary column table is allowed to insert/read/truncate the data, exists for the duration of the session and from the global temporary column table is automatically dropped when the session is terminated. Global temporary column table can be dropped only when the table does not have any record in it”.

      Data dropped, but not table.

    2. Ahmed says:

      Hi Admin,

      I am very much confused in using DUMMY table, what are all the fields we can have in dummy table, means how will i get the fields names whose data can be accessed from DUMMY table.

    3. Antonio says:

      Is it possible to create a global temporary table within a function in HANA?
      example

      CREATE FUNCTION “test1” (param VARCHAR(30)
      RETURNS TABLE (“return” text VARCHAR)
      LANGUAGE SQLSCRIPT AS
      AUX int;
      CREATE GLOBAL TEMPORARY COLUMN TABLE temp_table( result INTEGER );
      .
      .
      .

      • Admin says:

        Hi Antonio,
        User defined functions are read-only functions that are free of side effects. That is to say that DDL or DML statements are not allowed within the function body.
        So, you cannot create a global temporary table in function.

        Regards,
        Admin

    Leave a Reply

    Your email address will not be published. Required fields are marked *

    Current day month ye@r *

     © 2017 : saphanatutorial.com, All rights reserved.  Privacy Policy