Creating an SAP HANA table with SQL Script

Welcome to the next SQL script for SAP HANA tutorial for beginners. In this tutorial, we will create a SAP HANA table with SQL script. This is a continuation of our series outlining the different ways in which you can create tables in SAP HANA. It is highly recommended that you read the first part where we discuss the graphical method.

To recap, the main methods to create a table are:

  1. Using the graphical method that has been already discussed.
  2. Using SQL Script which will be discussed here.
  3. Using the HDB tables method discussed in a separate tutorial.
  4. Using the HDBDD method discussed in a separate tutorial.(Recommended method in non XSA projects).
  5. Using the HDBCDS method which would be discussed in a future tutorial(Recommended method in XSA projects).

SAP HANA Table: HANA Create statement Syntax

Let’s create the exact same table that we did using the graphical method with fields Customer number, First name, Last Name and revenue in USD. The syntax to be used is

CREATE <table _type> “ <table _name>”                             

(

“<field1>”  <data type 1> <Nullability_criteria>

“<field2>”  <data type 2> <Nullability_criteria>

“<fieldN>”  <data type N> <Nullability_criteria>

PRIMARY KEY (“<primary_key_field1>”,”<primary_key_field2>” )

) ;

What does this even mean?



<table_type> can have many values but the important ones are ROW and COLUMN. I have discussed what these are in a separate tutorial. The default is a ROW table and hence even if do not write ROW, a row table will get created. But for analytical applications, we prefer to use COLUMN tables and hence here, <table_type> should have COLUMN in all analytical requirements.

<table_name> is a self-explanatory statement wherein you specify the table name which you wish to create. The important point to note here is that the schema name where the table needs to be created needs to be specified in this statement. For example if you need to create the table ORANGES under schema FRUITS, then <table_name> would be “FRUITS”.”ORANGES”

<field>  refers to the name of the fields you wish to add in this table.

<data type> refers to the data type of the field you are trying to add.



<Nullability_criteria>  takes the values NOT NULL if the field is a primary key or if you want to restrict the incoming data in this table to have no NULL values.

<primary_key_field> takes all the primary key field names separated by a comma.

Note: Always be careful with the position of the brackets. Try to follow the syntax shown above. Also end all SQL statements with a semicolon.;

SQL Script example to create a SAP HANA table

With this logic, our SQL code for a new table (which we cleverly name as CUST_REV_SQL) with the same structure as the one we built in the graphical table tutorial would look as below.

CREATE COLUMN TABLE “0TEACHMEHANA”.”CUST_REV_SQL”

     (“CUST_ID” NVARCHAR(10) NOT NULL ,

       “FIRST_NAME” NVARCHAR(20),

       “LAST_NAME” NVARCHAR(20),

       “REVENUE_USD” INTEGER ,

       PRIMARY KEY (“CUST_ID”)) 

;

Open the SQL editor by clicking somewhere in the schema tree and then pressing the SQL button as it becomes active.

SAP HANA TABLE SQL SCRIPT TABLE

Let’s copy this code to the SQL editor and press the execute button marked below or press the F8 button after selecting the code.

SAP HANA TABLE SQL SCRIPT TABLE

The message for successful execution is shown below. Now we refresh the Tables folder to see this newly created masterpiece.SAP HANA TABLE SQL SCRIPT TABLE

The new table has been created as seen and double clicking on it reveals that the structure is also as we intended it to be.

SAP HANA TABLE SQL SCRIPT TABLE

This concludes this tutorial on creating tables with SQL script in SAP HANA. Please read the next part of Table creation tutorial – Creating HDB tables. You will really find it fascinating.

If you liked the content, please support this website by clicking the share buttons below to share it across social media and also subscribe for updates on new content.

Happy Learning.

<<Previous Tutorial                                                                                                               Next Tutorial>>

Content Protection by DMCA.com
Tagged , , , , , , , .

10 Comments

  1. Hi,

    I was able to activate table when fields are mentioned with out double quotes.

    My HANA version is 1.0 SP12

    Regards,
    Venkata

  2. Shyam, Could you please update content regarding table_type may be in older version default table type is ROW_STORE and now its Column store. I’ve double checked in system, please correct me if I’m wrong!

    • It is not necessary to define a key in a table but ideally we should. A key enforces uniqueness. For example, a table with employee ID as key would only allow 1 row of data per employee ID.. thus removing possibilities of duplication.
      NOT null condition makes sure that the the field is never left unfilled. This comes in handy for mandatory columns ..like Date of Birth as an example.

    • The HANA internally stores each datatype in a columnar datatype CS_INT is the datatype for INTEGER’s storage. It is not required to provide this information. Now that I think about it, I am going to remove the CS_INT from this tutorial as well. It causes un-necessary confusion.

  3. Hi shyamuthaman,

    I am now reading your tutorial series on SQL Script for 30 min now.
    It is the best tutorial on SQL Script I have seen on the internet so far.

    Great clarity, scrope and right sense of humor.
    Can not stop reading.

    All the best and thank you!

Leave a Reply