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

    15 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.

    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