Skip to Content
author's profile photo Keven Lachance
0
Keven Lachance

Manually Reset/Reload sequence number

Posted on Feb 21, 2013 at 01:44 PM 715 Views

I would like to know if there is a way to reload the next available number in a sequence.

I want to do mass importation of data in my schema after it has been created.I import data from another database and put that into hana. I cannot use the schema next value for the key because it is irrelevant and wrong : if my source database start at 10, and skip to 12 and the follow to 13, that's the key that I want in Hana.

After the importation, the problem is that the sequence stays at the same value and fail because of unique constraint of the primary key.

Examples are always good to understand and reproduce :

--Create the database structure

create table seqtable (idkey int primary key);

--sequence example taken from sap hana reference guide

create sequence sq_table reset by select ifnull(max(idkey), 0) +1 from seqtable;

--Data import. Import any value from a source system.

insert into seqtable values (1);

insert into seqtable values (2);

insert into seqtable values (3);

insert into seqtable values (4);

--Data Import finished.

--Start using the database for new records

insert into seqtable values (sq_table.nextval);

Could not execute 'insert into seqtable values (sq_table.nextval)'

[301]: unique constraint violated: Table(SEQTABLE), Index(_SYS_TREE_RS_#1378238_#0_#P0)

the sq_table sequence return 1, then 2, 3,4 and they all fail the primary key constraint.

I want a way to say to the sequence : reset yourself. Do your Reset cause by query to get the next available number.

According to the reference guide, I can restart the database ... but this is not a good solution for many reasons.

http://help.sap.com/hana/html/sql_create_sequence.html

RESET BY

During the restart of the database, database automatically executes the RESET BY statement and the sequence value is restarted with the specified value from the statement after RESET BY.

If RESET BY is not specified, the sequence value is stored persistently in database. During the restart of the database, the next value of the sequence is generated from the saved sequence value.

Any idea ?

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

3 Answers

  • Best Answer
    author's profile photo Keven Lachance
    Keven Lachance
    Posted on Feb 21, 2013 at 02:05 PM
    4

    Creating the sequence that are part of the database after the import is kind of weird.

    I guess that I will create all the database and sequence,

    start using the system OR do the data import

    Then drop and recreate all the sequences. That should work too.

    However, recreating the sequence after the data import does not work : the sequence is created with the next value of 1 instead of 5. Do you have any idea ?

    RESET BY wa already there in the rev 41 that I use.

    Add a comment
    10|10000 characters needed characters exceeded

    • Keven Lachance Keven Lachance
      Mar 06, 2013 at 09:43 PM

      To work around the issue, what I did in an program is :

      1. Create the sequence with a Reset by query.

      To refresh the columns,

      2. Get the select * from sys.sequences.

      3. in the precedent select, you got the reset_by_query. Execute that query to get the next available number.

      4. refresh the sequence by manually specifying the restart with. Keep the reset by to help you recalculate where the sequence should be.

      alter sequence sq_xxx RESTART WITH yyy reset by zzz.

      Even if the reset by query does not work, I can use that query to easily check in an _automated_ process what is the next available number.

    Comment on This Answer
  • author's profile photo Fernando Da Ros
    Fernando Da Ros
    Posted on Feb 21, 2013 at 01:53 PM
    0

    Hi Keven,

    You must only create the sequence after load as you didn't used it.

    Move this to the end of your inserts...

    --sequence example taken from sap hana reference guide

    create sequence sq_table reset by select ifnull(max(idkey), 0) +1 from seqtable;

    You did when the table seqtable is empty so it's fine to have 1 for sequence object.

    Regards, Fernando Da Rós

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Henk Binnendijk
    Henk Binnendijk
    Posted on Dec 09, 2014 at 02:09 PM
    0

    you can (re)set a value of the sequence by simply call the nextval function:

    determine the current value of the sequence: select schema.sequencename.CURRVAL from dummy;

    then determine the value you want to have (most of the time by select the max from the table)

    then set the currval:

    select schema.sequancename.NEXTVAL+x from dummy;

    the +x does the trick. you can even decrease the value if you want.

    Example

    source table has your ID at 10. but you want to have it start on 15 then:

    select schema.sequancename.NEXTVAL+5 from dummy;

    when finished, check by run the sql: select schema.sequancename.CURRVAL from dummy.

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.