sql for sap hana
Post on 15-Dec-2015
21 Views
Preview:
DESCRIPTION
TRANSCRIPT
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
top related