How to use sql with sap hana?
SQL is a database query language which you use to interrogate databases with. There is a standard SQL which is common to all databases and then there are specific SQL Syntaxes related to each type of database such as Sap Hana, Oracle, MS SQL and so on. Learning the Basic SQL is good to start with. From Basic SQL you will be able to do everything you need to as a beginner. For example, creating tables, inserting updating, deleting data from tables, writing queries. Here is a simple online learning SQL site. Getting to SQL hana specific features is what we are going to guide to. The easiest way to write any sap hana SQL is to use a related interactive SQL tool such as sap hana studio sql console.
Using Sap Hana Studio SQL Console
Where to get sap hana SQL Script Reference?
Like many other database type, sap hana uses standard but also specific sap hana SQL database query language syntaxes. You will find here all relevant subjects that is good to know whenever you start a development project.
The following links will give you and overview how to use structured Query Language. Go to Sap hana specific "SQL references" whenever you get a sap hana SQL syntax error.
What is sap hana SQL Script for?
Sap hana SQLScripts are basically self-content blocks of structured Query Language (SQL) which can be executed as a single unit. There are different types of script or unit. The SQL standard script unit, which would have sequential set of individual and independent SQL Queries, would be saved as an external text file. It could be executed either cutting and pasting the file content or calling the file as a parameter. Other type of unit are created within the database. Those SQL script units are called procedures and functions. They can be called and executed within an sql tool such as hana studio SQL console or within other scripts.
Various sources of Sap Hana SQL references :
Working with sap hana tables
Using and maintaining table data, is a very important part in a structured hana data model. Standard tables and temporary tables have different purposes. Normal tables hold permanent data where as temporary tables are used to store and process intermediate results. Any sap hana temporary table data is lost at the end of a session. It is basically used in stored procedures.
Row tables and column tables have an serious impact in sap hana performance. Sap Hana Column tables have the advantage of being more compact and more performant in comparison to row table. More about sap hana row and column tables
More about sap hana row and column tables
Basic tips regarding tables :
- Creating a new table
- Dropping ( removing ) a table
- Generating a "Create SQL statement" for a table.
How to use SQL with hana database?
If you are new with Sap Hana and you are dealing with SQL on Sap Hana databases, well you will be happy to know, SQL syntaxes are standard across all databases. However, you will find now and then, non-standard specific commands for a particular database. Although, it is possible to operate Hana DDL via the Hana studio interface, it is advisable to perform any Data Definition Language (DDL) Statements via SQL Script. It is easier to trace, update and debug what has been done that way.
The following section will show you various tips and syntaxes regarding SQL in SAP HANA. You will find out how to retrieve a table column description in sap hana, how to extract a DDL to recreate a Table.
Data Definition Language
Commands related to creation and alteration of objects such as table :
CREATE : to create objects ( table, synonyms, procedure, ...)
ALTER : to modify objects ( table, ...)
DROP : to remove objects ( table, synonyms, procedure, ...) from the database
TRUNCATE : to remove all records from a table
COMMENT : to add comments to a column
RENAME : to rename an object
Data Manipulation Language
Commands related to data manipulation in a table object:
SELECT : to retrieve data from the a table
INSERT : to insert data into a table
UPDATE : to updates data within a table
DELETE : to delete data from a table
MERGE : to insert or update following conditions
CALL : to execute a pprocedure
Data Control Language
Commands related to access privileges to objects:
GRANT : to gives privileges to access and manipulate data and database
REVOKE : to remove privilege assigned to a user
Commands to manage data changes.
COMMIT : to save data changes permanently into a table
ROLLBACK : to wipe out any data changes made since last command : COMMIT
SAVEPOINT : a fixe point a transaction can be rollback to
Sap hana table descriptions and DDL
How to drop tables in sap hana?
There is no particularity in the way to get rid of a table. Dropping table is done via a standard SQL syntax. Be aware there is no way to recover a table that would have been dropped accidentally. All data is lost for that table and database integrity may be at stake. You may have to recover the entire database from the latest backup.
Drop table "A_USER"."A_TABLE";
How to change sap hana table type ROW to type COLUMN?
Modifying a table type to another one is very easy and straight forward. You might want to perform the operation if you have created a row stored table by accident. Do not wait to do the changes as Row tables will take lots of memory space as more data is loaded into it.
-- change table type
ALTER TABLE A_TABLE ALTER TYPE ROW;
ALTER TABLE A_TABLE ALTER TYPE COLUMN;
How to manage tables in sap hana?
Sap hana tables represent units of data storage. They are defined with a name and a table type. The table types are ROW or COLUMN. As Sap hana is an In Memory database all tables are uploaded into memory. Most of them will be of type column. Column tables are smaller than similar row tables in comparison.
Managing sap hana tables requires creating, altering, loading data, dropping, retrieving, inserting, updating and deleting data.
How to query and update tables in sap hana?
In this section you will discover how to manipulate data from a sap hana table using SQL syntaxes. Like any type of databases, Sap hana allows you to select, update, delete and insert data into tables.
How to select data from a sap hana table?
The select command retrieves and shows data from a sap hana table. Data can be filtered using the WHERE clause.
-- Get all data
SELECT ID, NAME, TOWN FROM A_GLOBAL_TEMP_TABLE;
-- Get some data
SELECT TB_IDX_NAME, TB_DATEIN
select * from "A_USER"."A_TABLE"
where TB_ID = 'A03'
or TB_ID = 'A01');
How to get the first x rows of data from a query in sap hana?
There are different ways to get a result. The easiest way is to use the syntax "TOP" in the select statement.
SELECT TOP 3 * FROM USERS;
SELECT TOP 1 USER_NAME FROM USERS;
How to get the last x rows of data from a query in sap hana?
There are different ways to get a result. The easiest way is to use the "TOP" syntax and the " ORDER BY .... DESC " statement.
SELECT TOP 3 * FROM USERS ORDER BY USER_NAME DESC;
SELECT TOP 1 USER_NAME FROM USERS ORDER BY USER_NAME DESC;
How to insert data into a sap hana table?
The insert command adds data to a sap hana table. The action can be rollback if the commit has not been applied. The command has to be committed in order to apply the new data immediately and permanently.
-- Standard insert statement
INSERT into "A_USER"."A_TABLE" values ('A01','TB_NAME01','TB_IDX01',1,'','' );
INSERT into "A_USER"."A_TABLE" values ('A02','TB_NAME02','TB_IDX02',2,'SAP','' );
INSERT into "A_USER"."A_TABLE" values ('A03','TB_NAME03','TB_IDX03',3,'','2017/01/15' );
-- Insert data from a select statement
INSERT INTO "A_SCHEMA"."A_REPORT_TABLE"
SELECT A."DATE", A."CLIENT", A."HARWARE", A."SALES_QTY", A."SALES_VALUE", B."CLIENT_TYPE"
FROM "A_SCHEMA"."CLIENT_TABLE" A
LEFT OUTER JOIN
ON (A."CLIENT" = B."CLIENT")
WHERE A."DATE" = '2017/01/15';
How to delete data from a sap hana table?
The delete command remove data from a sap hana table. The action can be rollback if the commit has not been applied. The command has to be committed in order to apply changes immediately and permanently.
DELETE FROM ARC_BOOK_TB
WHERE BID > 10000;
How to truncate all data from a sap hana table?
The truncate command remove all data from a sap hana table immediately and permanently. NO Data can be rollback. Data is lost for ever.
TRUNCATE TABLE ARC_BOOK_TB;
How to update data from a sap hana table?
The update command changes data from a sap hana table. The action can be rollback if the commit has not been applied. The command has to be commited in order to apply changes immediately and permanently.
SET BTITLE = (SELECT distinct BTITLE
WHERE B.TID = A.TID )