Inserting Multiple Rows of Data With One Insert

[Former Member]

In MySQL, I can insert multiple rows of data with one insert statement by simply comma separating the VALUES part of the statement. How can I batch/bulk/multiple row insert into HANA with one INSERT statement?

[lbreddemann]

Closing this one due to no action of the OP.

[lbreddemann]

Hi Kevin,

nope - HANA does not support INSERT/UPDATE batching via a single SQL command.

However, you can create command batches via JDBC (and I assume also ODBC, but haven't tested that).

- Lars

[rama_shankar3]

Lars,

With JDBC, can we do full roll-back? For example, we have 3 update statements and I want to rollback 1st two updates based on a condition if 3rd update fails?

Please let me know.

thanks,

Ram

a

[lbreddemann]

Hi Rama,

of course, regardless of using JDBC or any other client, HANA's transaction support will always roll back the whole transaction.

- Lars

[former_member991653]

This Jun 21, 2019 at 08:21 AM comment indicates that this sort of insert/update batching is now supported. If it is now supported, when did that change?

I'm using JDBC and would prefer to use prepared statements for these inserts. Unfortunately that is not an option due to performance problems.

[former_member184768]

Hi Kevin,

I don't think currently this feature is available in HANA, but you can use INSERT INTO .. SELECT statement for multiple rows insert.

May be as a work around you can use something like:

INSERT INTO ..

    (SELECT ... from dummy

     UNION

     SELECT... from dummy

     UNION

     SELECT... from dummy

      )

Regards,

Ravi

[richard_birenheide]

I just tried this with a HANA DB Version:

1.00.82.00.394270

where it works.

-Richard

[lbreddemann]

Hi Richard,

the INSERT INTO (SELECT... ) approach always worked.

What's not possible with SAP HANA is something like this:

insert into users (user_name, user_id) values (('LARS', 1), ('PETER', 2));

Such multi-inserts are not supported by SAP HANA.

In case you do have a working example where this actually works, please share

- Lars

[richard_birenheide]

Ah, then I misunderstood your comment downthread. I considered the statement above to be a single SQL command, which may be wrong from a referential point of view. I am not too experienced in SQL therefore maybe the misunderstanding.