How to insert a lot of record

[Former Member]

Dear Experts,

i have an SQL file containing 50.000 INSERT SQL instructions that populate 300 Hana Tables .

I'm executing this file via "SQL Console" in the "Hana Studio" on a "SAP Hana Platform" envitoment but it is taking so long time .

Are there any way to speed up this INSERT Process ?

How can i create a job and run it in background ?

Thank you

[lbreddemann · Accepted solution]

Hi Carlo,

if you actually want to just take the 50K INSERT statements and execute them, then there's basically only one way to speed this up:

Set the AUTOCOMMIT mode to FALSE in HANA SQL Studio or hdbsql.

(hmm, actually I doubt that you will be able to load and execute such a large SQL text file into the SAP HANA Studio SQL Editor...).

Much better performance-wise would be to apply some additional pre-work to the text files and convert them into a plain CSV file.

That way you can easily use the IMPORT command or the IMPORT from frontend feature in SAP HANA Studio.

- Lars

[Former Member · Accepted solution]

Hi Lars,

this is the correct answer, using AUTOCOMMIT = FALSE  the process is fast .

Thank You!

Maurizio

[nikhil_joy2]

Hi Carlo,

Do you mean to say 50K separate INSERT commands with hardcoded values? Or by a code your are executing 50K INSERT statements? Please clarify if you have data in any system or file type.

Regard,

Nikhil Joy

[Former Member]

Hi Carlo,

As SQL script file, I think that it may not have better way, you only exec it in sql console or hdbsql command, if it is flat file, such as txt or cvs data file, it has better way.

Hope this can help you.

Regards,

Jerry

[Former Member]

Hi Carlo,

I assume you are using BODS for ETL, if so, you can create job in DS and use Script where you can use these insert statements.

Then you can schedule this job in background.

Syntac to execute SQL from DS is

SQL ('<Datssource Name>','<Sql statement>');

Regards

Sagar