SAP Support

  • SAP support
  • Dump files for support

SQL

  • SQL guide line
  • SQL references
  • Using SQL
  • Using Tables
  • Using Procedure
  • Queries and Updates
  • Using variables
  • Anonymous blocks

SQL Hands on

  • Get sql scripting skills

Tables

  • Using tables efficiently?
  • Different ways to create a table
  • Row & Column table differences
  • Row & Column tables storage
  • Create Column Table syntax
  • Create table examples
  • Ways to insert data into tables
  • Ways to select data from tables

SAP Hana Jobs

  • Finding a job in sap hana

Sap Hana training courses

  • Online training courses

Tips & Tricks

  • About tables.
  • Unexpected table type

Administration tasks

  • Daily Tasks
  • Start/Stop database
  • Start/Stop Hana System
  • hdbrsutil process tips and tricks
  • Stop or cancel SQL queries
  • Refresh data from another tenant database

Administrator User

  • Overall user administration
  • Unlock SYSTEM User
  • Unlock User
  • Activate, Unlock a user
  • Reset System user lost password
  • Create administrator User
  • Create multiple users in one go
  • Copy a user
  • Check user active or not
  • Check user status
  • Check password expiration date
  • Reset User connection attempt
  • Update password expiration date
  • Update login until date
  • Change password on first login
  • Change password on next login
  • Show Password expiration date
  • Update Password expiration date
  • Connection & password security
  • Disable password validity period
  • New user password
  • Keep old password
  • Retrieve User details
  • Create any user type
  • Drop User
  • Update User
  • De-activate/Activate User
  • Troubleshoot User

Standard Users

  • Create User with SQL script
  • Create User via Hana Studio

Technical users

  • General Tips
  • Backup User
  • Modeler User
  • Data provisioning User
  • Cockpit User
  • A user to unlock users

Configuration

  • Configuration
  • Kill long queries automatically

Log & Trace Files

  • Log and trace directories
  • Investigate backup log content

About Memory

  • Memory analysis
  • Peak Used memory
  • Peak Memory between 2 dates
  • Physical memory
  • Memory Investigation Scenarios
  • Allocated memory
  • Used memory
  • Used memory - Tables
  • Used memory - System Tables
  • Used memory - Column tables
  • Used memory - Delta storage
  • Used memory - Row tables
  • Used memory - Services
  • Used memory SQL
  • Used memory - code & stack
  • Table storage comparison

Memory Operations

  • Adding physical memory

Disk

  • Reclaimable space
  • Reclaim space

Security

  • Security Guide Line
  • Before updating a password

Privileges

  • Roles and privileges
  • Grand read access on a schema
  • Privileges to unlock user
  • Grant privileges

Backup & Recovery

  • Overview
  • Configuration
  • Backup
  • Restore/Recover
  • Checking
  • Log backup directories
  • Get backup catalog size
  • Restore database manually
  • Analyse backup log file

Troubleshooting

  • Overview
  • Common issues
  • Bugs
  • Errors
  • Performance
  • Memory
  • Alerts
  • Scripts and Procedures
  • Volume Disk full
  • Long queries
  • Alert 10: passwd authentication
  • Alert 65 log backup running too long
  • Alert 413: cannot reuse password
  • Alert 414: password to be changed
  • Alert 431: password expiration
  • 503 Service not available
  • System User lost password
  • Backint backup failure
  • Hana System cannot start
  • Locked transactions

Analyses

  • Bugs
  • Errors
  • Performance
  • Memory
  • Alerts
  • Scripts and Procedures

Investigations

  • Get Sap hana version details?
  • Get to Log and Trace directories
  • Get log backup directories
  • Get Sap Hana default Port
  • Get Session Details
  • Get Open Session list
  • Get hana user status
  • Get dump files diagnosis

Hdbsql

  • Using hdbsql?
  • Executing SQL
  • Formating output

Hana Cockpit

  • Overview
  • General Configurations?
  • URL to access Tenant
  • URL to access SystemDB
  • Troubleshooting
  • Administration
  • Monitoring
  • Open & Access Cockpit

Hana Studio

  • Installation
  • Guideline
  • SQL Console

Hdbuserstore

  • Using hdbuserstore

SapControl

  • Using sapcontrol

Free Hands on

  • Free sap hana version

Hardwares

  • Hardware for sap hana
  • Possible Architectures

Smart Data Integration

  • Configure Smart Data Integration
  • Create Data Provisioning Project Users

FAQ

  • Frequently Asked Questions

upv6r1-201911

menu.
Best sap hana training
Digg Email Facebook Google Pinterest StumbleUpon Tumblr Twitter VK linkedin

SAP NOTES FOR "SQL SCRIPTING "

SQL Reference
SQL Statements
Execute SQL Statements in SAP HANA Studio
×

Learning SQL Insert

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.

Using hana sql console
using hdbsql

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 hana variables
sap hana sql tips
sap hana contract deal

Back to top

Copyright Ⓒ 2019 Best Sap Hana Training. All rights reserved
Recommended web hosting : www.siteground.com