Basic SQL for SAP HANAApplies to:

SAP HANA 1.0 SP04

Summary

This wiki covers the commonly used SAP HANA Database implementation of Structured Query Language (SQL). This

wiki has been written entirely with SAP HANA Database - SQL Reference Guide as the sole reference.

Table of Contents

Basic SQL for SAP HANA

Data Types

CREATE SCHEMA

CREATE TABLE

INSERT

IMPORT FROM

ALTER

DROP

Related Content

Data Types

Classification Data Type

Datetime types DATE, TIME, SECONDTIME, TIMESTAMP

Numeric types TINYINT, SMALLINT, INTEGER, BIGINT, DECIMAL, REAL, DOUBLE, FLOAT

Character string

types

VARCHAR, NVARCHAR, ALPHANUM, SHORTTEXT

Large Object types BLOB, CLOB, NCLOB

CREATE SCHEMA

CREATE SCHEMA SCHEMA1;

A Schema with the name SCHEMA1 is created

CREATE TABLE

COLUMN TABLE CREATIONCREATE COLUMN TABLE SCHEMA1.TABLE1       (       I INTEGER PRIMARY KEY,       A VARCHAR(10)NULL       );

A Column Table "TABLE1" is created under Schema "SCHEMA1".CREATE COLUMN TABLE SCHEMA2.TABLE2               (                I INTEGER NOT NULL,                A VARCHAR(10) NULL,        B VARCHAR(10) NULL,        PRIMARY KEY (I, A)       );

A COLUMN Table "TABLE2" is created under Schema "SCHEMA2" with clustered Primary Keys on Integer "I" and

Varchar "A".

ROW TABLE CREATIONCREATE TABLE SCHEMA1.TABLE3       (       I INTEGER PRIMARY KEY,       A VARCHAR(10)NULL       );

A ROW Table "Table3" is created under Schema "Schema1" 

INSERT

INSERT INTO SCHEMA1.TABLE1 VALUES ( 1, 'Rishi', 'Muhuri');

Inserts Values 1, 'Rishi', 'Muhuri' into Table "TABLE1" under Schema "SCHEMA1"INSERT INTO SCHEMA1.TABLE1 (I, A)  SELECT I, A FROM SCHEMA2.TABLE2;ORINSERT INTO SCHEMA1.TABLE1 (SELECT * FROM SCHEMA2.TABLE2);

Inserts COLUMNS I and A from TABLE2 (of SCHEMA2 ) into COLUMNS I and A of TABLE1 (of SCHEMA1).

or

Inserts ALL COLUMNS of TABLE2 (of SCHEMA2) into ALL COLUMNS of TABLE1 (of SCHEMA2)

NOTE: The columns types of the two tables must match both length and type.

IMPORT FROM

To import from a csv file , we need to have a control file .IMPORT FROM '/tmp/cntfile.ctl'

This points tothe control file 'cntfile.ctl' at the location /tmp

and

cntfile.ctl should containIMPORT DATAINTO TABLE SCHEMA1.TABLE1FROM 'data.csv'FIELDS DELIMITED BY ';'RECORD DELIMITED BY '\n'OPTIONALLY ENCLOSED BY '"'ERROR LOG 'ErrorLog'

This imports data in the table "TABLE1" from 'data.csv' located in the same directory as the control file. In case of error ,

it is logged in the file 'ErrorLog'

ALTER

ALTER TABLE SCHEMA1.TABLE1 ROW

Changes Column Table "TABLE1" to Row typeALTER TABLE SCHEMA1.TABLE2 COLUMN

Changes Row Table "TABLE2" to Column type

DROP

Drop TableDROP TABLE TABLE1

Drop SchemaDROP SCHEMA SCHEMA1