Insert all default values

[Former Member]

Hi, I'm trying to add SAP Hana database support to my ORM. There are cases when entity / table is defined with all columns set to DEFAULT and user tries to insert rows into such table with any values specified. Also, primary key is generated thus isn't being specified as well.

Other databases either support following syntax: "INSERT INTO "table" ("id", "name") VALUES (DEFAULT, DEFAULT)", either support following syntax: "INSERT INTO "table" DEFAULT VALUES". And it seems SAP doesn't support such syntax and I can't find a workaround in the internet. Thank you.

[lbreddemann · Accepted solution]

TL;DR answer:

SAP HANA (up to HANA 2 SP04) does not support the DEFAULT/DEFAULT VALUES syntax.

The way to insert default values in HANA is to leave out the columns that should get default values from the insert columns list.
Obviously, with an identity column in the table, this leaves no columns in the insert columns list, which in turn is incorrect insert syntax.

This is a bit problematic, since the HANA documentation states that HANA complies to ANSI SQL Feature F221 "Explicit Defaults" which requires that default values can be obtained by either leaving the target column out of the insert list (which is what HANA supports) or via the DEFAULTS keyword that you have seen in other DBMS.

My guess is that there is no technology blocker here, so maybe opening a support message pointing out this shortcoming can get the feature onto the backlog or increase its priority.

Other than that, I have to agree with Mike, this is a fairly non-sensical operation and the explanation that it is the result of an ORM-framework test-suite kind of underlines this notion.

[lucian2 · Accepted solution]

Any update here?

None of these seem to be working:

INSERT INTO "product" DEFAULT VALUES;
INSERT INTO "product" VALUES();
INSERT INTO "product"() VALUES();

Is there any workaround that I could use?