How to create a primary key column and fill it with integer values on HANA SQL

I searched but only could found partial answer to this question The goal would be here to create a new ID column on an existing table. This new column would be the primary key for the table and I simply want it to be filled with integer values from 1 to number of rows.

What would be the query for that? I know I have to first alter table to create the new column :

ALTER TABLE <MYTABLE> ADD (ID INTEGER);

Then I could use the series generator :

INSERT INTO <MYTABLE.ID> SELECT SERIES_GENERATE_INTEGER(1,1,(number of rows));

Once the column is filled I could use this line:

ALTER TABLE <MYTABLE> ADD PRIMARY KEY ("ID");

I am sure there is an easier way to do this

Solution

You wrote that you want to add a "new ID column to an existing table" and fill it with unique values. That's not a "standard" operation in any DBMS, as the usual assumption is that records are created with a primary key and not retro fitted.

Thus, "ease" of operation for this is relative to what else you want to do. For example, if you want to continue using this ID as a primary key for further operations, then using a once-off generator function like the SERIES_GENERATE_INTEGER or a query won't be very helpful since you have to avoid duplicates of already existing values.

Two, relatively easy, options come to mind:

Using a sequence:
```
create sequence myid;
update <table> set ID = myid.nextval;
```
And for succeeding inserts:
```
insert into <table> (id, ..., ...) VALUES (myid.nextval, ..., ...) ;
```
Note that this generates a value for every existing record and not a predefined set of size X.
Using a GUID By using a GUID you generate a unique value every time you call the 'SYSUUID' function in SAP HANA. check docu here

Something like
```
update <table> set ID = SYSUUID;
```
should do the trick here.

Subsequent inserts would simply call the function for values of ID.