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
Working with Tables in SAP HANA


In this article, we will learn following:
    • How to create, update and delete tables in SAP HANA.
    • How to see definition and data preview of tables.
    • How to change column type of a table.
Create Table in SAP HANA:

Create a table using standard SQL
    1. Select your Schema. Right click and select "SQL Console". Otherwise you can also click on "SQL" button on top panel as shown in below figure.
      SAP HANA Create Table
    2. Copy the SQL statement below in SQL editor.
CREATE COLUMN TABLE TEST_TABLE1 (
        ID INTEGER,
        NAME VARCHAR(10),
        DESCRIPTION VARCHAR(100),
        PRIMARY KEY (ID) );

  • Click on Execute (or F8)
    SAP HANA Create Table
  • Right-click on Tables under the Navigator Tab and click on Refresh to display the name of the newly created table.
    SAP HANA Create Table
  • Right click on the table and click on "Open Definition" to see the table details.
    SAP HANA Create Table

  • Create a table with the SAP HANA Studio tools:
      1. Select your Schema. Right click and select "New Table"
        SAP HANA Create Table
      2. Enter the Table Name
      3. Choose the Table Type, e.g. "Column Store"
      4. Enter the required table fields, data types, Key characteristics, etc. by clicking on the "+" sign below, and click on the Create table icon (or F8) when ready.
        SAP HANA Create Table
    Enter data in table and display data preview:

      1. Open SQL Editor and copy below SQL statements. Click on Execute icon (or F8).
        INSERT INTO TEST_TABLE1 VALUES (1, 'A', 'A12345');
        INSERT INTO TEST_TABLE1 VALUES (2, 'B', 'B12345');

        SAP HANA Create Table
      2. Right click on the table and select "Open Content". This will show the content of the table as shown in image below.
        SAP HANA Create Table

    Change Column Type of Tables:

      1. You can change the table type from COLUMN to ROW or vice-versa. Open the SQL editor and copy the below SQL statement. Click on Execute (or F8).
        ALTER TABLE TEST_TABLE1 ALTER TYPE ROW;

      2. Open the table definition by right clicking on table and selecting "Open Definition". Check that the table type has been changed from COLUMN to ROW.
        SAP HANA Create Table

    You should now be able to create and work with tables in SAP HANA. If you still have any doubt or question please leave a comment or contact us using "Contact Us" option in the Menu..

    Note: In HANA there are 2 types of tables. Row based and Column based.
    For more details on these, read Row based and Column based tables in HANA




    Support us by sharing this article.



    Explore More
    Close X
    Close X

    16 thoughts on “SAP HANA Create Table

    1. Usha says:

      It would be great if you had explained Row Based table and Column based table.
      Also, the bebefits or the difference between them.

      • Admin says:

        Hi Usha,
        Please check the below article for Row based and Column based table
        http://saphanatutorial.com/column-data-storage-and-row-data-storage-sap-hana/

      • Kevin says:

        Hiya,

        Thank u For sharing this unique article. Definitely a life saver.

        this is the situation:
        – HANA 1.0 SPS12
        – There is no PRELOAD flag set for any table
        – 3TB of column tables are loaded during normal workload
        – The RELOAD feature is active.
        During a HANA revision update (after the executables were updated) the delivery units are going to be imported. If the parameter “reload_tables = true” was set before the update process gets started, the system will reload all tables (3TB) which were loaded during normal operation and recognised by the reload-mechanism.
        Due to the high I/O load, the delivery unit import will be extremly slow and will just complete after the table reload has finished.
        My idea was to set the reload_tables parameter to “false” before the update begins, then do the update and set the parameter back to “true” afterwards in combination with a HANA restart SAP HANA training USA .
        Does the HANA system lose the information which tables were loaded, when I set the parameter to “false” or are they saved somewhere and will be noticed when I re-activate the parameter?
        My fear is, that the information is lost and when I reactivate the parameter the system will rescan all loaded tables at this moment (which are just a few) and so all the other tables needed by the SAP system for normal work don’t get loaded at start-up time.
        If the information gets lost: Is there a way to manually save the information which tables are loaded at the moment and to restore this information to the system after the parameter gets activated again?
        Excellent tutorials – very easy to understand with all the details. I hope you will continue to provide more such tutorials.

        Obrigado,
        Kevin

    2. Naru says:

      Hi,

      I have gone through the article “article for Row based and Column based table” but I am unable to understand the real benifts of the column storage apart from compression due to few distinct values.

      As per my understanding, even in a row level storage when we issue the select
      select sum(sales) from sales where date >= 2012-01-01 then only two columns will be accessed those will be sales and date.

      Kindly correct me if I am wrong.

      • Admin says:

        Hi Naveen,
        “select sum(sales) from sales where date >= 2012-01-01”

        In row based, it will have to traverse thru complete memory strip to fetch the sales and date columns. Plus since sales values are saved non-continuous location, aggregation will be slow.

        In row based, it does not have to traverse thru entire memory strip. Sales values are saved in continuous location, hence aggregation will be faster.

    3. Antonette says:

      Great Tutorial! 🙂

      What are the authorizations needed to create tables? I get the following error:

      SAP DBTech JDBC: [258]: insufficient privilege: Not authorized

      Regards
      Antonette

    4. Raghavandra says:

      Hii,let me know where to get the environment to execute the table and row implementation or we have to virtually analyse it .

      • Admin says:

        Hi Raghavandra,
        I did not get your question. Are you asking where you can execute SQL statement? or Are you looking for row/column implementation of table?
        Please elaborate.

    5. Chandru says:

      Hi,
      When i do date preview in eclipse, i get an error message ‘No Authorization to access the resource /sap/bc/adt/datapreview/ddic’. However i have full authorization for authority object ‘S_ADT_RES’.What could be the issue?

    6. Abhi says:

      CREATE COLUMN TABLE ‘NEW_TABLE’ AS (SELECT * FROM OLD_TABLE);

      Will it works?
      or if any modification needed in syntax?

      plz write working syntax to copy data from old to new table

      • Admin says:

        Hi Abhi,
        Yes, the syntax is correct. You may try LIKE statement as alternative.

        Syntax 1:
        create column table NEW_TABLE as (select * from OLD_TABLE)

        Syntax 2:
        create column table NEW_TABLE like OLD_TABLE with data;

        Regards,
        Admin

    7. vijayalakshmi says:

      could you please let me know what is the table type option while selecting the type of the table.

    8. Sudarmani says:

      guys
      is there any other way to insert data into the table in hana other than using SQL statement.?

    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