Primary Key with IDENTITY and Delta Functionality with UPSERT/REPLACE together in a column.

[sourabh17]

Hello Everyone,

Need your help!!

I have a requirement where i need to auto-increment a primary key and also have delta functionality enabled for the same table. Details are as below.

I have to update and insert new values in a table at the same time. The data will be loaded from a flat file directly to HANA Columnar table. The requirement is something like if the Primary Key column is empty for a particular row that row should be inserted with a new Primary key in the table and if the Primary key is already available the all the columns for that row should be updated with the new values.

I have gone through the https://blogs.sap.com/2014/06/04/quick-note-on-identity-column-in-sap-hana/ and it seems IDENTITY with UPSERT/REPLACE suits my requirement. But when i try to use UPSERT/REPLACE with IDENTITY it only allows to add new entries for the Existing entries it throws error as "SAP DBTech JDBC: [269]: too many values: line 1 col 61 (at pos 60)".

Tables created:

CREATE COLUMN TABLE "AUTO_INCREMENT_TEST" 
("PRIMARY_KEY" BIGINT PRIMARY KEY NOT NULL GENERATED BY DEFAULT AS IDENTITY, "PRICE" INTEGER);

CREATE COLUMN TABLE "KUMARSOU"."UPSERT_CHECK_TEMP" 
("PRIMARY_KEY" BIGINT PRIMARY KEY NOT NULL, 
"PRICE" INTEGER)

I am loading the data first into the UPSERT_CHECK_TEMP table and then using the UPSERT command to update the AUTO_INCREMENT_TEST table.

UPSERT "AUTO_INCREMENT_TEST" SELECT PRIMARY_KEY,PRICE FROM "UPSERT_CHECK_TEMP"

Thanks!

Sourabh