Insert data into target table with limit and offset value

[Former Member]

Hi All,

i am trying to bulk load data from view to table.I have 1 record in source view.but i want to insert records into set of 10K.

for example:

insert into target_table

select col1,

col1,

col2

from view limit 10000;

but above code is not working.

I want to insert set of 10000 records 10 times.
Please share your suggestions.Thanks in advance.

[Former Member]

Hi Mark,

Thanks a lot for concern.

But i have already 1 lakh of record stored in view that i need to insrt into target table.i just want to process 10 K records at a time.Here i am using Sap Hana DB and insert statement i need to write inside procedure to insert the bulk records.

[Mark_A_Parsons]

Now I'm confused ...

- first post you mention the view has 1 row (ie, 'select * from view' will return 1 row)

- now you say view has 100K (lakh) rows

If the view has just 1 row, see my examples of using a cartesian product to generate 10K copies of the 1 row in the view.

If the view has 100K rows and you want to insert the 100K rows into target_table ... in batches of 10K at a time ... and looking up the HANA SQL syntax for select/limit ... have you tried:

     insert target_table select * from view order by .... limit X offset Y

Where:

X/Y = 10000/0 for first insert

X/Y = 10000/10000 for second insert

X/Y = 10000/20000 for third insert

...

X/Y = 10000/90000 for last/tenth insert

NOTE: I'm guessing that to be 100% sure you don't insert any duplicate records you'll need the order by clause.

A brief search of the HANA/SQL reference doesn't explicitly mention if variables can be used in place of the limit/offset values (ie, X/Y placeholders in my example - above), but this post - HANA pagination - Navigate forward and backward... | SCN - appears to be doing something similar to what you want and there's a comment about using parameters.

If HANA doesn't allow variables for the limit/offset values, perhaps there's a way to implement dynamic queries?

-----------------

If the above doesn't help then I'd suggest posting your question in a HANA-related forum, eg:

SAP HANA Developer Center | SCN

or