Various ways to insert data into sap hana tables

The "Insert" command is another fundamental statement in a relational database. It allows loading data into the sap hana tenant database. Records can be inserted in any sap hana tables in various ways. The "insert" statement is the standard command used in the SQL programming language. Import command is another way to load data into multiple tables but data source cannot be part of a specific data validation procedure.

Following examples are based on the following table structure.


  CREATE COLUMN TABLE table_name_ex
  (
  col1 INT PRIMARY KEY,
  col2 varchar(20),
  col3 int
  );

Here are 2 common SQL insert syntaxes:

 
  -- Example 1 -
  -- Columns are explicitely defined and positionned.  
  -- Data will be recieved into corresponding defined column. 
  -------------------------------------------------------  
  --
  INSERT INTO table_name_ex 
  ( col1, col2, col3 ) 
  VALUES 
  ( 1234, "text", 22546278  );

 
  -- Example 2 -
  -- Columns names are NOT defined.  
  -- Data will be assigned according to the table column sequences 
  -- Be aware : Data could be loaded to the wrong column if the data position in the "values" section  
  -- does not refer to the underline table column position. 
  -------------------------------------------------------  		
  -- The following example positioned wrongly a character string into an integer column.  
  -- The first underlined column "col1" being the PRIMARY KEY
  --
  INSERT INTO table_name_ex  
  VALUES  
  ( "text", 1234,  ....  );

Using Sap Hana Studio SQL Console

About Sap hana SQL INSERT Statement

The SQL insert statement usage in sap hana is exactly the same as any relational databases. The aim is to be able to load data into tables. The difference between other data loading methods is the fact that, not only data can be coming from various sources, but data can follow a validation process before going into a table.

Inserting data into a sap hana table

In real life, data will be loaded via insert statements either via stored procedures, shell scripts, SQL Scripts or any type of compiled programs. Data is validated and integrity is maintained within this type of structures. Otherwise any SQL interactive tools such as Hana "SQL console" have little use apart from testing and validating procedure syntaxes. You could break a complete data integrity using INSERT command syntaxes with such a tool connected to a production database.

How to use SELECT with INSERT in Sap Hana?

We can look at the question from different angles. In the first place, within a procedure structure data is collected from a SQL Query and values are placed into variables. Those variables would then be going through a validation process before finishing in the INSERT statement. Keep in mind, certain validations could occur at the query level using the case statement. In the second place, there is no validation up front, data is retrieved immediately from a select query and the result is straight away passed across to the INSERT statement.

Here are table names and data for all following next examples:

 
  CREATE COLUMN TABLE MYSCHEMA.STUDENTS_TB
  (
  STUDENT_ID    INT PRIMARY KEY,
  STUDENT_NAME  varchar(20),
  CLASS         varchar(20)
  );
  
  INSERT INTO MYSCHEMA.STUDENTS_TB (STUDENT_ID,STUDENT_NAME,CLASS) values (1,'PAUL','SPORT');
  INSERT INTO MYSCHEMA.STUDENTS_TB (STUDENT_ID,STUDENT_NAME,CLASS) values (2,'PETER','MATH');
  INSERT INTO MYSCHEMA.STUDENTS_TB (STUDENT_ID,STUDENT_NAME,CLASS) values (3,'ISABEL','SCIENCE');
  
  CREATE COLUMN TABLE MYSCHEMA.CLASSES_TB
  (
  STUDENT_ID    INT PRIMARY KEY,
  STUDENT_NAME  varchar(20),
  CLASS         varchar(20),
  START_DATE    DateTime
  );

  CREATE COLUMN TABLE MYSCHEMA.ACTIVITY_TB
  (
  ACTIVITY_NAME  varchar(20),
  TUTOR_NAME     varchar(20),
  STUDENT_NAME   varchar(20),
  STARTDATE      DateTime
  );
  
  CREATE COLUMN TABLE MYSCHEMA.TUTORS_TB
  (
  TUTOR_ID    INT PRIMARY KEY,
  TUTOR_NAME  varchar(20),
  SUBJECT     varchar(20)
  ); 
  
  INSERT INTO MYSCHEMA.TUTORS_TB (TUTOR_ID,TUTOR_NAME,SUBJECT) values (1, 'CHARLIE','SCIENCE');

Using variables with Sap Hana INSERT statement

Variables are pretty much used with SQL script programming. Variable declarations are written at the beginning of any procedures and functions. Wherever data comes from, it is often moved to variables to start with. Variables can hold all sorts of data. Validations can be performed in all sorts of ways through variables. It is also possible to build and compute new results before placing variables into an insert statement.

 
  Example 1.  
  ---------------- Insert data from variable content into a table
  
  DO BEGIN
  
  DECLARE VSTUDENT_ID int;
  DECLARE VSTUDENT_NAME VARCHAR(20);
  DECLARE VCLASS VARCHAR(20);
  DECLARE VSTARTDATE DATE := '2019/02/27';
  
  -- Example based on a 1 row query result
  --
  SELECT STUDENT_ID, STUDENT_NAME,CLASS into VSTUDENT_ID, VSTUDENT_NAME, VCLASS  
  from STUDENTS_TB
  where student_name = 'ISABEL';
  
  -- Data validation process before going into a target table would happened here
  -- IF ..
  -- ...
  -- ELSE
  -- ...
  -- END IF;
               
  INSERT INTO CLASSES_TB values (VSTUDENT_ID, VSTUDENT_NAME, VCLASS, VSTARTDATE );
  
  SELECT * FROM CLASSES_TB;
  
  END;

  
  | STUDENT_ID  | STUDEN | CLASS   | START_DATE                    |
  | ----------- | ------ | ------- | ----------------------------- |
  |           3 | ISABEL | SCIENCE | 2019-02-27 00:00:00.000000000 |

INSERT into sap hana table directly from query result

Multiple records of data can be inserted into a table straight from a SQL query. This method is very useful. First of all, the SQL Insert Script from select statement is totally self-content. There is no external influence while data is being processed. Secondly, data can be checked and validated as part of the query. Inserted Record set relies entirely on the final query result.

 
  Example 2.
  -------------------- Insert data directly to a table through a query result content
  
  TRUNCATE TABLE MYSCHEMA.CLASSES_TB;
  
  INSERT INTO CLASSES_TB 
  SELECT  STUDENT_ID, 
          STUDENT_NAME, 
          CLASS, 
          '2019/02/27' as start_date
  FROM STUDENTS_TB
  where student_name = 'ISABEL';
  
  SELECT * FROM CLASSES_TB;
  
  | STUDENT_ID  | STUDEN | CLASS   | START_DATE                    |
  | ----------- | ------ | ------- | ----------------------------- |
  |           3 | ISABEL | SCIENCE | 2019-02-27 00:00:00.000000000 |

Insert data from a sap hana sub select query

Inserting data from a sub select has several advantages. Data from different tables can be combined and inserted into a unique table. Validation can be done as part of the overall SQL select and sub select script. The SQL script is self-content and therefore can be easily tested and reused elsewhere.

 
  Example 3.
  -------------------- Insert data directly to a table through a simple sub select query
  
  INSERT INTO MYSCHEMA.ACTIVITY_TB
  SELECT ACTIVITY_NAME, TUTOR_NAME, STUDENT_NAME, STARTDATE
     FROM ( SELECT SUBJECT as ACTIVITY_NAME,
                   TUTOR_NAME,
                   (SELECT STUDENT_NAME from  MYSCHEMA.students_tb where CLASS = 'SCIENCE') as STUDENT_NAME,
                   '2019/02/27' as STARTDATE
            FROM MYSCHEMA.TUTORS_TB
            WHERE SUBJECT = 'SCIENCE'
          );
  
  -- Check up inserted data
  
  SELECT ACTIVITY_NAME, TUTOR_NAME, STUDENT_NAME, TO_CHAR(STARTDATE,'YYYY-MM-DD') as STARTING_DATE FROM  MYSCHEMA.ACTIVITY_TB
  
  | ACTIVIT | TUTOR_N | STUDEN | STARTING_D |
  | ------- | ------- | ------ | ---------- |
  | SCIENCE | CHARLIE | ISABEL | 2019-02-27 |
  
  -- check up tutor data
  
  select * from MYSCHEMA.TUTORS_TB;
  
  | TUTOR_ID    | TUTOR_N | SUBJECT |
  | ----------- | ------- | ------- |
  |           1 | CHARLIE | SCIENCE |

 
  Example 4.
  -------------------- Insert data directly to a table through a sub select query 
  --                   with validation and data transformation
  
  TRUNCATE TABLE MYSCHEMA.ACTIVITY_TB;
  UPDATE TABLE MYSCHEMA.STUDENTS_TB  SET STUDENT_ID=99999 WHERE STUDENT_NAME = 'ISABEL';				
				
  INSERT INTO MYSCHEMA.ACTIVITY_TB
  SELECT ACTIVITY_NAME, TUTOR_NAME, STUDENT_NAME , STARTDATE
     FROM ( SELECT SUBJECT as ACTIVITY_NAME,
                   TUTOR_NAME,
                   (select 
                       case
                          when STUDENT_NAME = 'ISABEL' then
                              '(ID:'||STUDENT_ID||') '||STUDENT_NAME
                       end STUDENT_NAME 
                   from  MYSCHEMA.students_tb) as STUDENT_NAME,
                   '2019/02/27' as STARTDATE
            FROM MYSCHEMA.TUTORS_TB
            WHERE SUBJECT = 'SCIENCE'
          );
  
  -- Check up inserted data
  
  SELECT ACTIVITY_NAME, TUTOR_NAME, STUDENT_NAME, TO_CHAR(STARTDATE,'YYYY-MM-DD') as STARTING_DATE FROM  MYSCHEMA.ACTIVITY_TB
  
  | ACTIVIT | TUTOR_N | STUDENT_NAME      | STARTING_D |
  | ------- | ------- | ----------------- | ---------- |
  | SCIENCE | CHARLIE | (ID:99999) ISABEL | 2019-02-27 |
  
  -- check up tutor data
  
  select * from MYSCHEMA.TUTORS_TB;
  
  | TUTOR_ID    | TUTOR_N | SUBJECT |
  | ----------- | ------- | ------- |
  |           1 | CHARLIE | SCIENCE |

Advanced sap hana SQL INSERT from SHELL SCRIPT and hdbsql

Using Shell scripts is a very good way to check, monitor and validate sap hana tenant database content. It is not database dependent, so you can reuse the overall shell against other databases. In most situations, it will be used for sap hana backup and sap hana administration purposes. Depending on company policy, it can also be used for applications. The following example shows part of a shell script which inserts a new student details into a database table.


  #/bin/bash
  # --------------------------- Insert data into table using hdbsql in shell script
  #
   . . .
   . . .
  GV_LAST_ID=""
  GV_STUDENT_NEW_ID=""
  GV_SQL_INSERT=""
  GV_HDBSQL="hdbsql"
  GV_INDATE='2019/03/03'
  GV_SQL="SELECT TOP 1 'VALUE:'||STUDENT_ID  FROM MYSCHEMA.STUDENTS_TB ORDER BY 1 DESC";
  
  #
  # hdbsql -i -->  Sap hana environment ID
  # hdbsql -d -->  Sap hana tenant database name
  # hdbsql -U -->  Sap hana userstorekey
  # 
  
  sudo su - fasadm << EOF | grep "VALUE:" | awk -F: '{print $2}'  | awk -F'"' '{print $1}' > /tmp/rec.dat
  ${GV_HDBSQL} -a -x 
  \c -i 00 -d ${PARA_4_TENANTDB} -U ${PARA_3_APPLI_USKEY} "${GV_SQL}"
  EOF
  
  GV_LAST_ID=`cat /tmp/rec.dat`
  GV_STUDENT_NEW_ID=`expr $GV_LAST_ID + 1`
  GV_SQL_INSERT="(INSERT INTO MYSCHEMA.STUDENT_TB (STUDENT_ID, STUDENT_NAME, CLASS, START_DATE) VALUE (${GV_STUDENT_NEW_ID},${PARA_1_NAME},${PARA_2_CLASS},${PARA_3_DATE} )"
  
  #
  # hdbsql -a -->  output with no header
  # hdbsql -x -->  run quietly (no messages, only query output)
  # 
  sudo su - fasadm << EOF 
  ${GV_HDBSQL} -a -x 
  \c -i 00 -d ${PARA_4_TENANTDB} -U ${PARA_3_APPLI_USKEY} "(${GV_SQL_INSERT}"
  EOF
  if [ $? -eq 0 ];then
     echo "User ${GV_ENAME_TO_UPDATE} inserted successfully."
  else
     echo "User ${GV_ENAME_TO_UPDATE} insertion failed"
  fi