As of SPS8, SAP introduced the ability to create auto generated columns that increment by using IDENTITY. Auto generated columns only apply to Column table types. To create an auto generated column, simply add GENERATED BY DEFAULT AS IDENTITY to the column as shown in Listing 1 below.
If you need to start the identity value from a particular number, you can add START WITH 1000 as an argument to IDENTITY where 1000 is the value to start from.
The identity value increments by 1 when data is inserted into the table but this can be changed using INCREMENT BY 2 where 2 is the value to increment by.
If you have an existing table and you want to add an auto generated column, you can use the ALTER TABLE syntax as shown below.
Listing 1
CREATE COLUMN TABLE Employee(
EMPLOYEE_ID BIGINT GENERATED BY DEFAULT AS IDENTITY,
FIRST_NAME VARCHAR(30),
LAST_NAME VARCHAR(30)
)
If you need to start the identity value from a particular number, you can add START WITH 1000 as an argument to IDENTITY where 1000 is the value to start from.
Listing 2
...
EMPLOYEE_ID BIGINT GENERATED BY DEFAULT AS IDENTITY(START WITH 1000),
...
The identity value increments by 1 when data is inserted into the table but this can be changed using INCREMENT BY 2 where 2 is the value to increment by.
Listing 3
...
EMPLOYEE_ID BIGINT GENERATED BY DEFAULT AS IDENTITY(START WITH 1000 INCREMENT BY 2),
...
If you have an existing table and you want to add an auto generated column, you can use the ALTER TABLE syntax as shown below.
Listing 4
ALTER TABLE Employee ADD(EMPLOYEE_ID BIGINT GENERATED BY DEFAULT AS IDENTITY)
HANA DB Articles
-
JSON_TABLE Function
In this article, we'll take a look at how to use the ++JSON_TABLE++ function to represent JSON data stored in a regular column as a relational table.
31 March 2023 - 3732 views -
Connect To HANA DB Using Rust
Learn how to connect and query a HANA database using Rust with the hdbconnect package.
21 February 2023 - 3353 views -
If Table Exists Function
In this article, I explain how to create a function to determine if a table exists in SAP HANA.
31 October 2019 - 9659 views -
Connect To HANA DB Using NodeJs
This article explains how to connect to a SAP HANA system using the Node HANA client library.
28 October 2019 - 7773 views -
Connect To HANA DB Using Python
This article explains how to connect to a SAP HANA system using the Python hdbcli package.
11 October 2018 - 8693 views -
Connecting To SAP HANA Using PHP ODBC
This article describes how to install and configure PHP odbc to connect to a SAP HANA system on Ubuntu 16.
01 December 2017 - 8293 views -
Connect To HANA DB Using Java
This article describes how to connect to a HANA DB system using the ngdbc.jar Java driver.
03 November 2017 - 5712 views