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