Scribd
Upload
222 views3 pages

Sap Hana-Sql Statements

This document outlines various ALTER statements that can be used to modify tables and columns in SAP HANA, including adding, removing, and changing columns; converting between row and column tables; adding primary keys; and adding comments. Examples are provided for creating tables and columns, viewing data, converting storage type, adding columns, changing data types, adding primary keys, dropping columns, and adding comments on tables and columns. Limitations on data type changes are noted depending on the table storage type.

Uploaded by

sai haritha kavuluru
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
222 views3 pages

Sap Hana-Sql Statements

This document outlines various ALTER statements that can be used to modify tables and columns in SAP HANA, including adding, removing, and changing columns; converting between row and column tables; adding primary keys; and adding comments. Examples are provided for creating tables and columns, viewing data, converting storage type, adding columns, changing data types, adding primary keys, dropping columns, and adding comments on tables and columns. Limitations on data type changes are noted depending on the table storage type.

Uploaded by

sai haritha kavuluru
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 3

SAP HANA: ALTER statements

To Create Table:
   create column table "KABIL_PRACTICE"."DEMO"

   (

   "ID" integer,

   "NAME" Nvarchar(35)

   );

To View the data from the table:


   select * from "KABIL_PRACTICE"."DEMO";

To alter Column Store to Row Store table:


   Alter table "KABIL_PRACTICE"."DEMO" Row;

To alter Row Store to Column Store table:


   Alter table "KABIL_PRACTICE"."DEMO" Column;

Alter table to add an additional column.          


   Alter table "KABIL_PRACTICE"."DEMO" Add ("CITY" Nvarchar(35));

Alter table to change the data type of an existing column.


   Alter table "KABIL_PRACTICE"."DEMO" Alter ("CITY" varchar(35));

Note:
         For column table, only increasing the size of a column data type is allowed to prevent data loss.

         For example, changing from NVARCHAR (20) to NVARCHAR (10) or from INTEGER to TINYINT
raises an error.

         For row table, only increasing the size of VARCHAR and NVARCHAR type column is allowed. Other
data type changes are not allowed.

Example:
To add a Salary column:
   Alter table "KABIL_PRACTICE"."DEMO" Add ("Salary" Decimal(10,2));

To alter a Salary Column data type:


   Alter table "KABIL_PRACTICE"."DEMO" Alter ("Salary" Decimal(15,2));
            It raises an error like feature not supported: cannot change the field length:
Salary

To Alter Existing Column with NOT NULL


   Alter table "KABIL_PRACTICE"."DEMO" Alter ("ID" integer NOT NULL);

   Adding a NOT NULL constraint to an existing column is allowed if either of the following cases
are true:

         The table is empty.

         The default value is specified when the table contains data.

         The table does not contain a NULL-value in that column.

To Add Primary Key for Existing table column


   Alter table "KABIL_PRACTICE"."DEMO" Add primary key ("ID");

To Add Primary Key for Multiple Columns in an Existing table


   Alter table "KABIL_PRACTICE"."DEMO" Add primary key ("ID","NAME");

To Drop Primary Key for Existing table column


   Alter table "KABIL_PRACTICE"."DEMO" drop primary key;

Alter table to Drop column.                           


   Alter table "KABIL_PRACTICE"."DEMO" Drop ("CITY");

COMMENT ON:
         This statement is used to add descriptive comments to tables, views or their individual columns in
SAP HANA system.

         Comments are a useful way to record a meaningful description of schema tables/views, and their
columns, for future reference.

         We can also remove comments using same statement.

Examples:
To create COMMENT ON for DEMO table in
“KABIL_PRACTICE” Schema
   COMMENT ON TABLE "KABIL_PRACTICE"."DEMO" IS 'DEMO Table for Alter
Statements';

Note:

            we can see this comment by Right click your table from the corresponding schema and
click open definition. A new tab will open from that select Further Properties, you can see
the comment of your table.
To Remove comment on Table
   COMMENT ON TABLE "KABIL_PRACTICE"."DEMO" IS null;

To create COMMENT ON ID Column from DEMO table in


“KABIL_PRACTICE” Schema
   COMMENT ON COLUMN "KABIL_PRACTICE"."DEMO"."ID" is 'EMPLOYEE_ID';

Note:

            we can see this comment by Right click your table from the corresponding schema and
click open definition. A new tab will open with that comment will be shown in the
corresponding column.

To Remove comment on Column


   COMMENT ON COLUMN "KABIL_PRACTICE"."DEMO"."ID" is null;

To Rename Column
   Rename column "KABIL_PRACTICE"."DEMO"."ID"  To "E_ID";
To Rename Table
   Rename Table "KABIL_PRACTICE"."DEMO" To "DEMO_TABLE";

SAP RECOMENDED LINK FOR SQL IN HANA

https://help.sap.com/viewer/4fe29514fd584807ac9f2a04f6754767/2.0.00/en-US/
f327b70cae564c53a766367a8aad0164.html

You might also like