Thanks Brian for the reply.
I guess CSVREAD is the only option for bulk insert.
Can we restrict reading number of rows from csvfile ?
Can we call CSVREAD in a loop till EOF to read limited number of lines ?
Something like:
1.Reader keeps writting record to csv file.
2.If 1000 records present then insert into table else wait till 1000
records.
Regards,
Bharath
On Sunday, 29 July 2012 00:20:01 UTC+5:30, Brian wrote:
>
> There are a couple of options - since it's not indexed, it may or not be
> any faster.
> a.) If your DB has a jdbc driver, use a linked table, query the linked
> table and do the group/order by with H2.
> b.) If you don't have a jdbc driver, you can treat CSVREAD() _as_ a table
> without re-persisting it.
> ex: given a csv like:
> COMMON,DIFF
> "a",1
> "a",2
> "b",3
> "b",4
>
> In h2, this works fine:
> select common,count(diff) from CSVREAD('test.csv') group by common order
> by common;
>
> -Brian
>
>
> On Friday, July 27, 2012 3:36:30 AM UTC-7, bharath phatak wrote:
>>
>> I am totally new to H2 database and have been following your posts in
>> learning H2 database and working with it.
>> I am stuck with a problem and need your advice .
>>
>> We treat H2 database as a tmpDB for running the query which are not
>> supported by our underlying DB. Ex: Order by and Group By are not supported
>> by our DB.
>> To get this done we can use normal way like running the basic query on
>> our DB ,storing the results in csv format and bulk insert into H2(CSVREAD)
>> and run the query once again on H2 to get the final results.
>>
>> The problem with above approach is that it includes double I/O . The data
>> can include million records.
>>
>> What we think:
>>
>>
>> - Understand CSVREAD code and customize it to our needs.
>> - The data keeps coming-in in buffer .
>> - Without manipulating it,directly insert into H2 like CSVREAD.
>> - Assuming that CSVREAD reads data and stores in a buffer before
>> inserting into data, we need to implement CSVREAD code for inserting the
>> data from buffer.
>>
>>
>> Please advice on how to proceed further. It will be very helpful if you
>> could provide point us to the CSVREAD code.
>>
>>
>> Also please let us know if you have any better approach for bulk insert
>> of data into H2 for buffer.
>>
>> Thanks in advance.
>>
>> Regards,
>> Bharath
>>
>
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To view this discussion on the web visit
https://groups.google.com/d/msg/h2-database/-/5rxstRB3ncMJ.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
