CODEWITHSUNDEEP
hana
jriedel
jriedel

Reputation: 13

SAP HANA hdbsequence trigger reset_by manually

In the project I'm working at the Id for certain insert statements is managed by hdbsequences. Now I want to create a sequence for another table that already has existing data in it and I want it to start with the max id value of the data of that table.

I know I could just manually set the "start_with"-Property to it but that is not an option because we need to transport the sequence to another system later where the data in that corresponding table is not the same as on the current system (therefore the ID is different).

I also know of the "reset_by"-Property in which I can select the max value of the table, the problem is that I don't know how to trigger that explicitly.

What I already found out, is that the "reset_by"-Property is called whenever the database is restarted, but unfortunately that is not also not an option because we can't reset the database to not disrupt the other systems.

Thanks in advance for your time and help.

Upvotes: 1

Views: 1253

Answers (1)

Florian Pfeffer
Florian Pfeffer

Reputation: 269

You can do an ALTER SEQUENCE and set the value to be used by the next sequence usage with the option "restart with".

For instance (schema name and sequence name have to be replaced):

alter sequence "<schema name>"."<sequence name>" restart with 100;

The integer value behind the "restart with" option has to be set to the value which has to be used next. So in case your last ID is 100, set it to 101. 101 is the value returned by the next NEXTVAL call on the sequence.

Upvotes: 1

Related Questions