Skip to Content
avatar image
Former Member

Identity column increments when using update in MERGE INTO statement.


I have a scenario where i am using MERGE INTO statement to propagate the changes from one table to another, the tables are identical but for one more IDENTITY column present in the target table. Now when i run merge into it works fine as to updating the records and inserting a new record but the identity column value generated for the new record jumps by the number of records that were updated by the query and this does not happen when i use just an UPDATE statement. Can anyone please explain why is this happening? Is it on account of the MERGE into statement being new to HANA 2.0 or is that the intended behavior? Thanks in advance!

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Sep 30, 2017 at 03:21 AM

    This is probably an implementation side effect of the MERGE INTO statement. And it is correct, as neither sequences or IDENTITY columns promise to be gap-free number dispensers. In fact, that is precisely an attribute that these features don't provide. What you get from both is that you have a convenient and fast way to generate new (not yet provisioned) number.

    Add comment
    10|10000 characters needed characters exceeded