Various ways to insert sap hana data using SQL Script

The "Insert" command is another fundamental statement in a relation 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.

Here are 2 common SQL insert syntaxes:

-- Following examples are based on the following table structure.

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

																						
 -- 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,  ....  );

About Sap hana SQL Script INSERT

The usage of SQL insert script 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 2 different angles. In the first place, 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. 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.

Table names and data for all examples below :


 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';

 SELECT STUDENT_ID, STUDENT_NAME, CLASS into VSTUDENT_ID, VSTUDENT_NAME, VCLASS  
 from STUDENTS_TB
 where student_name = 'ISABEL';

 select 'col1 : '|| VSTUDENT_ID as col1,
        'col2 : '|| VSTUDENT_NAME as col2,
        'col3 : '|| VCLASS as col3 from dummy; 
             
 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 DateIn
 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 |

Sap hana SQL Tools

Two SQL tools for sap hana. Sap hana Studio SQL console which has to be installed on PC or notebook. Hdbsql which is a sap hana os command line tool accessible on the server.

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 ibnto 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

SAP NOTES FOR "SQL SCRIPTING "