Problem with auto increment column in SAP Hana

[former_member439903]

Hello guys,

I have the problem that i cannot use the auto increment column id.

I try to load csv files via ctl files in Hana.

My Hana Version: 1.00.121.00.1466466057

Here is the csv file I use.

This is the the error message i receive.

And here is the code I used. If the ID row is out commented there is no problem to import the names in the table. With the ID row in the code, no names are loaded in.

SQL CODE

CREATE COLUMN TABLE "<schema>"."Name" (
	"ID" INTEGER NOT NULL PRIMARY KEY GENERATED BY DEFAULT as IDENTITY,
	"Name" NVARCHAR(25)) UNLOAD PRIORITY 5 AUTO MERGE
;

IMPORT FROM CONTROL FILE '<path>name.ctl'
WITH
THREADS 64
BATCH 100000
TABLE LOCK
SKIP FIRST 1 ROW
FAIL ON INVALID DATA
;
------------------------------------------------------------
CTL File Code
IMPORT DATA INTO TABLE F079."Name"
FROM 'Namen.csv'
RECORD DELIMITED BY '\n'
FIELD DELIMITED BY ';'
ERROR LOG 'name.err'

[former_member182302]

The problem here is in the CTL file there is no column list mentioned and hence it is expecting the same columns to be present as in the target table. Could you try by mentioning the column list as in the below syntax?

Let us know if it works. Else you would have to use a intermediate table in between to complete the load.

Regards,
Krishna Tangudu

[former_member439903]

Hi Krishna,

thanks for your advice. I added the COLUMN LIST in the import statement but unfortunately it did not work.

Perhaps I have to use an intermediate table or I have to use the way I described earlier.

Regards,

Tobias

[BenedictV]

Did you try '\r\n' for record delimiter?

Try this for one row without header and let us know if you are able to load it, in which case the problem could be with the record delimiter.

[former_member439903]

Hello Benedict,

I also tried your solution but it did not work as well.

Regards,

Tobias

[former_member439903]

My solution for this problem is to create a new column table and then add a new ID column which is declared as identity. After the ID column is created I do an insert into B from A. The auto increment statement works how it should with this solution and it is quite fast even for larger tables.

CREATE COLUMN TABLE <Schema>.<B> LIKE <Schema>.<A>;
ADD (ID BIGINT  not null primary key generated by default as identity);

INSERT INTO <Schema>.<B> (Column1, Column2, Column3, ...)
SELECT * FROM <Schema>.<A>;