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";
Thank You for Visiting My Blog...
Share Your Comments...
https://sapguidanceconsultant.blogspot.com/
Post a Comment
Thanks for visiting our site : sapguidanceconsultant.blogspot.com
For Fast Response message us through "Contact Form".