Use sequence when inserting new records into a table

[patrickbachmann]

Hi folks,

I have a simple sequence that is generating a unique number.  Now I have a separate table that is being populated from a UI5 screen in SAP portal.  Whenever a new record is inserted in the table in HANA I want to use the sequence to update a field on the table to generate a unique ID number.  But I can't get the syntax to work. 

So without the sequence my insert looks something like this;

insert into "MYSCHEMA"."MYTABLE" values(12345,'JOE SMITH')

Now instead of this I want to somehow insert a unique sequence number in place of the first value.  ie: something like this (which does not work)

insert into "MYSCHEMA"."MYTABLE" values(SELECT "MYSCHEMA"."MYSEQUENCE".NEXTVAL FROM DUMMY,'JOE SMITH')

Is this possible?

Thanks,

-Patrick

[Former Member · Accepted solution]

Hi Patrick,

I'm not in front of the system..so I'm not quite sure if this will work.

But, could you try :

insert into "MYSCHEMA"."MYTABLE" values ("MYSCHEMA"."PACKAGE_PATH::MYSEQUENCE".NEXTVAL, 'JOE SMITH');

BR,

Nidhi

[Former Member · Accepted solution]

Unfortunately this would work in Oracle but a Insert/Update based on select statement is not yet supported in HANA

[patrickbachmann · Accepted solution]

Nidhi, your suggestion worked!  This is the syntax I used;

insert into "MYSCHEMA"."MYSEQUENCE" values("MYSCHEMA"."MYTABLE".NEXTVAL,'JOE')

Thank you so much!

Durgamadhab, your answer is also helpful, as I too would expect that syntax to also work.  Somehow this syntax from Nidhi is doing a very similar thing and yet allowed it to work so it's strange to me.

Thanks again!

-Patrick

[Former Member · Accepted solution]

Just FYI it is entirely possible to INSERT from a SELECT statement:

DROP TABLE QUICK_TEST;

CREATE COLUMN TABLE QUICK_TEST (F INTEGER);

INSERT INTO QUICK_TEST(F) SELECT 1 FROM DUMMY;

In the above, (F) is optional but can be used to insert into subset of available fields.

Also, alternate syntax:

SELECT 2 FROM DUMMY INTO QUICK_TEST;

And, bonus syntax for inserting into a scalar variable in SQLScript from SELECT:

SELECT TOP 1 SOME_FIELD INTO SOME_SCALAR FROM SOME_TABLE; -- just switch order of FROM/INTO

[Former Member · Accepted solution]

That's great! Glad to help.

BR,

Nidhi

[Former Member]

This message was moderated.

[Former Member]

Hello Patrick,

For your statment you need change your INSERT command to:

insert into "MYSCHEMA"."MYTABLE" values( "MYSCHEMA"."MYSEQUENCE".NEXTVAL, 'JOE SMITH');

Other possibility is create a TRIGGER over "MYSCHEMA"."MYTABLE"  with BEFORE event.

Bye,

Carlos Tack

[patrickbachmann]

Yes Carlos that's exactly what my next intention was;  to test trigger functionality so that the nextval doesn't have to be initiated from code on the portal side but an automatic trigger inside hana.  Thanks for the suggestion though.

-Patrick

[Former Member]

Hi Patrick,

The reason your insert is not working is HANA does not support INSERT using a single SQL command yet.

So unfortunately  you can not do a Insert using a select statement as value