How to add an Identity sequence to an existing column in Hana?

Question

I need to specify a id column that is generated automatically. The table already exists and has an enormous amount of data. Also it is very hard to get the permission for stopping the application on the production environment. I want to update the table on the fly.

Is it possible to change am existing table and enrich it with Identity functionality? Suddenly the official Documentation only states how to create an identity column, not how to alter it to an identity.

If not is it possible to simulate the functionality with sequences and triggers?

-- current schema of the table
CREATE TABLE nl_status(
    identificationnumber BIGINT NOT NULL PRIMARY KEY,
    status varchar(25)
);


--desired functionality
CREATE TABLE nl_status(
    identificationnumber BIGINT NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    status varchar(25)
);

Answer 1

You can try this one-time logic to convert it into an identity column.

--DROP TABLE TEST1;

CREATE TABLE TEST1 (COL1 **BIGINT NOT NULL PRIMARY KEY** , COL2 BIGINT );

INSERT INTO TEST1 (COL1, COL2) VALUES (1,1);

INSERT INTO TEST1 (COL1, COL2) VALUES (2,1);

SELECT * FROM TEST1; --2 ROWS

--DROP TABLE TEST1_BACKUP;

CREATE TABLE TEST1_BACKUP (**COL1 BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY** , COL2 BIGINT );

INSERT INTO TEST1_BACKUP SELECT * FROM TEST1; -- ONE TIME BACKUP

SELECT * FROM  TEST1_BACKUP;-- 2ROWS ALL COLUMNS INTACT AND DATA AS WELL

DROP TABLE TEST1;

**RENAME TABLE TEST1_BACKUP TO TEST1;**

INSERT INTO TEST1 ( COL2 ) VALUES (1); -- COL1 WILL GET GENERATED AUTOMATICALLY

SELECT * FROM TEST1; --COL1 WILL HAVE THE GENERATED VALUE;