What is the best way to implement the autoincrementation of primary key in HANA table?

[Former Member]

Hi,

I'm new in SAP HANA and I can't find any information about how can I get an auto incremented field in HANA table.

What I'am looking for is something like this:

CREATE TABLE T (ID INTEGER NOT NULL AUTO_INCREMENT, ....);

In SAP HANA SQL reference there are no such keywords as AUTO_INCREMENT, SERIAL, IDENTITY etc.

For now the only reasonable way I can think of is using of sequence, but I'd like to avoid

another SQL request just to get next sequence value.

Please, tell me what is the best way to obtain next unique value for primary key field?

regards

Marcin Pancewicz

[former_member184768 · Accepted solution]

Hi Marcin,

I don't think the SQL request to get the nextval for the sequence in the insert statement (sequence_name.nextval) will really have any impact on the performance. It would still be executed in the database along with the insert operation. It need not be another SQL statement by itself.

So I think with the current revision of HANA, using sequence might be the appropriate choice.

Regards,

Ravi

[Former Member · Accepted solution]

Hi Ravi,

When I try to use nextval in the insert statement, I got "feature not supported" error. Probably it's my fault, but I can't get it to work. Anyway, here is an example ('cid' is primary key):

INSERT INTO "campaigns" ("cid", "did", "aid", "name")

           VALUES (

             (SELECT "id_seq".NEXTVAL FROM DUMMY),

             '15', '7','campaign name'

            )

The error I got:

Could not execute 'INSERT INTO "campaigns" ( "cid", "did", "aid", "name"...'

SAP DBTech JDBC: [7] (at 523): feature not supported: sequence number not allowed here: line 3 col 15 (at pos 63)

I have no idea how can I write this query to get nextval.

Thanks for help,

Marcin

[former_member184768 · Accepted solution]

Hi Marcin,

Kindly change your statement as follows:

INSERT INTO "campaigns" ("cid", "did", "aid", "name")

           VALUES (

             (id_seq.NEXTVAL,

             '15', '7','campaign name'

            );

Regards,

Ravi

[Former Member · Accepted solution]

Ravi, you are great! Thanks a lot!

regards

Marcin

[Former Member · Accepted solution]

Hi Ravi,

is this a JDBC feature and somehow rewritten before executing the SQL query?

When I tried to use it, HANA studio did not accept the insert query.

Query:

INSERT INTO test ("id", "int_value", "float_value") VALUES (id_seq.NEXTVAL, 739, 239)

I get the error

Could not execute 'INSERT INTO test ("id", "int_value", "float_value") VALUES (id_seq.NEXTVAL, 739, ...'

SAP DBTech JDBC: [313] (at 78): invalid sequence: ID_SEQ: line 1 col 79 (at pos 78)

I also tried things like (select id.nextval from...) inside the insert. How do I have to rewrite the presented insert query above to make it work??

Best,

Martin

[Former Member · Accepted solution]

Hi Martin,

Did you create your sequence ID_SEQ? I looks like it's not existing...

Check http://help.sap.com/hana/html/sql_create_sequence.html - pay close attention to the behavior after a DB restart!

--Juergen

[Former Member · Accepted solution]

Hello Ravindra,

We are looking to join two tables and insert the records into 2 new tables based on some logic.  While moving the data, individual records in these two new tables should be tied with a unique id.

I thought of using Sequence. nextval to achieve this. There might be performance issues since each record would be inserted in the 2 tables indiviidually.

seq.nextval

{

INSERT INTO <NEW TABLE1> ...

(SELECT seq.currvall,COL2,COL3... from OLD TABLE 1 INNER JOIN OLD TABLE2 ON XYZ WHERE...)

Similar statement for NEW TABLE 2

}

Is there a way we can achieve this where everything is inserted in one go instead of multiple inserts?

Looking forward to a response.

Best,

Pankaj

[former_member184713 · Accepted solution]

I don't understand why you would want to do that.

Just start a transaction, do your two insert, then close your transaction. select outside your transaction will see either that you have the two or you have none.

[Former Member · Accepted solution]

What i understand from your post is that we should have a all or none approach for the 2 records so that the same sequence id is inserted in both.Thanks for this and this does benefit.

But my original question was that is there a way of doing multiple inserts in one go instead of hitting the database tables multiple times (approx 2 million times a day) . May be temp tables might help?

[yoppie_ariesthio · Accepted solution]

This message was moderated.

[Former Member]

Hello all,

Thanks for your discussing, I found a recent article about this topic on SPS08.

It seems that column table now can be supported with identity.

syntax like this:

create column table test_identity (ID int primary key generated by default as identity, name varchar(10));

Then we can try:

insert into test_identity(name) values('kevin');

insert into test_identity(name) values('angel');

insert into test_identity(name) values('kelvin');

insert into test_identity(name) values('angela');

Test the results:

select * from test_identity

ID;NAME

1;kevin

2;angel

3;kelvin

4;angela

To see how it works closer, please refer to the quick note above.

[Former Member]

Hi Kevin,

Finally they did it! Cool feature isn't it? Because before SPS08 one had to create a custom sequence with something like this:

EXEC 'CREATE SEQUENCE CLIENTS_SEQ increment by 1 start with 1 no cycle';

Thanks for sharing!

Best,

Andy.

[Former Member]

Yea kevin good find his helps allot

[Former Member]

Hi friends,

Its excellent.Worked for me.But i want to know how can we use this auto incremental in the following 2 data load methods:

1.CSV data load from Hana Studio

2.CTL loading method through Hana server.

Kindly help me in this

Regards

Nagarjuna

[Former Member]

Hi,

My first think is that you could use other tools when doing the data load if it's emergency.

For example, when you use CSV data load, you may try to first format the data by using Excel auto increment and load the data from HANA studio.

Would share more if have some more findings.

Cheers,

Kevin

[Former Member]

Hi,

are there any news about this ?

I need to load a target Table from a source table adding a new key as a new sequence .

Are there any way to do it in bulk insert ?

thank you

[former_member184713]

In the end, there was no way to set the nextval to the default of a key value.

We modified each of our queries to add the Key column and specify Sequence.nextval

Maybe Insert select would automatically add the key field to the table ?

CREate table Test1 (

value1 varchar (50)

);

Create table test2 (

idKey int primary key,

value2 varchar (50)

);

create sequence seq_test2 START with 1 increment by 1;

--test data

insert into test1 values ('test');

insert into test1 values ('test2');

insert into test1 values ('test3');

--Here you define the key value out of a sequence.

insert into test2 (idKey, value2)

select seq_test2.nextval, test1.value1

from Test1;

[woutdejong]

Same problem here. I really don't want to modify all our DML statements (INSERTS etc).

I'm trying this DDL :

CREATE TABLE mytable

(

  gid INTEGER PRIMARY KEY,

  col2 INTEGER DEFAULT SEQ1.NEXTVAL

);

Where SEQ1 is a simple SEQUENCE for auto increment.

I get this error:

SAP DBTech JDBC: [257] (at 73): sql syntax error: incorrect syntax near "SEQ1": line 5 col 24 (at pos 73)

Also tried SEQ1.NEXTVAL() or SYSTEM.SEQ1.NEXTVAL and even "SELECT SEQ1.NEXTVAL FROM DUMMY" but to no avail.

Docs for SEQUENCE says NEXTVAL canNOT be used in CREATE TABLE.

How really really unfortunate.

CREATE SEQUENCE - SQL Reference - SAP Library

"

You are not allowed to use CURRVAL and NEXTVAL in :

• The DEFAULT value of a column in a CREATE TABLE or ALTER TABLE statement

"

Guess we have to write a TRIGGER or FUNCTION for this simple thingy...

Any help appreciated.

Cheers, Wout

[Former Member]

Hi Wout,

It seems that it's not possible for the moment (I've got HANA ver. 52). I've played around with triggers and it doesn't allow to create neither INSERT nor UPDATE trigger which modifies a subject table.

This is very odd to me because I always guessed that auto incremental columns are kind of basic functionality for a RDBMS. I know that Oracle doesn't have it directly but it offers a way to create them though.

Hope they will fix is sometime in next revision.

Best regards,

Andy.

[woutdejong]

I'm on SP6 Rev68 and this autoincrement feature is not there yet. I don't think it will be in SP7 as well.

[former_member184713]

http://www.oracle-base.com/articles/misc/autonumber-and-identity.php

This article show how to use a trigger in Oracle to simulate the identity column.

This is pretty similar to the problem we are having.

I tried to to it in hana after converting the syntax, but I have the error

SAP DBTech JDBC: [7]: feature not supported: NEW transition variable is not allowed with BEFORE trigger.

Is there someone out there that know how to do it ?

[former_member184713]

In other database, we can insert into a table with the identity flag without specifying the value.

Example in mssql, it would be

Insert into Campanin

INSERT INTO "campaigns" ("did", "aid", "name")

           VALUES (

             ('15', '7','campaign name'

            );

there is nothing written for the cid column because the default is the next available number, and specifying default value is optionnal.

With the above solution, we need to specify the next value in the column, therefore modifying the sql request.

Is there a way to set the default value of a column to the next value of a sequence ?

something like this ?

CREATE ROW TABLE TEST2

(

ID                   int default TEST2_SEQUENCE.nextval,

  primary key ("ID")

)

[Former Member]

Yeah, this would be great to have. Especially since all the other databases offer it as well.