SAP HANA Table Types

In this article we will learn about Table Type in SAP HANA.
Introduction to Table Type:

A table type is

• Similar to a database table but do not have an instance
• Used to define parameters for a procedure that represent tabular results.

In HANA, with the help of SQLScript, we can create a Table Type.

How to Create a Table Type:

A table type is created using statement CREATE TYPE and can be deleted using statement DROP TYPE.

Syntax:
CREATE TYPE [schema.]name AS TABLE
          (name1 type1 [, name2 type2,...])

DROP TYPE [schema.]name [CASCADE]

Example:

1. Open HANA studio and run the below SQL statement to create a table type.
Replace SCHEMA_NAME with your schema.

CREATE TYPE SCHEMA_NAME.TT_SALES AS TABLE (
            SALES_AMOUNT DECIMAL,
            NET_AMOUNT DECIMAL,
            PRODUCT_NAME NVARCHAR(20),
            REGION_NAME NVARCHAR(20),
            SUB_REGION_NAME NVARCHAR(20)
);

2. After executing the statement you can go to THE schema and find the table type under Procedures -> Table Types section.

3. Double click on the table type to see the definition. 

4. Remember that we cannot add record to table type. If you try to insert record, you will get an error. 

INSERT INTO SCHEMA_NAME.TT_SALES VALUES (100, 'PROD-1', 'ASIA', 'INDIA');

Click here to go to Next Chapter. 2.1 Create/Alter/Drop Tables in HANA