Adding Auto Numbers To Table Columns

  • Syed Hussim
  • 14 March 2018
  • 1593
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.

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),
...
HANA DB Articles

Recent Articles

  • A JavaScript Implementation Of The Logo Programming Language - Part 2

    Part 2 of A Javascript Implementation Of The Logo Programming Language. In the previous article I explained how to develop a simple lexer. In this part we develop a TokenCollection class to help traverse the array of tokens returned from the lexer.

  • A JavaScript Implementation Of The Logo Programming Language - Part 1

    In this four part article, I explain how to develop the iconic Logo programming language in JavaScript. In this part, we discuss how to take a source input and convert it into a series of tokens.

  • Generating Web API Keys

    If you're building a REST API, chances are you're going to need to generate secure random API keys. In this article, I explain how to use the Node.js crypto module to generate random secure API keys.

  • Port Scanner

    A simple port scanner to scan a range of ports.

  • Stock Transfer Part 1

    The SAP B1 Service Layer provides an API to move stock between warehouses. This article explains the construction of the JSON payload data that is posted to the StockTransfers API.

  • Sorting Algorithms

    An introduction to sorting algorithms.

  • If Table Exists Function

    In this article, I explain how to create a function to determine if a table exists in SAP HANA.