Re: optimising data load

On Thursday 23 May 2002 03:40, Ron Johnson wrote:
> On Wed, 2002-05-22 at 16:05, John Taylor wrote:
>
> > So, how would I merge the data from the view with the data to copy in ?
>
> ??? Unless I am misunderstanding you:

I think you are misunderstanding me.
I have 300,000 rows to insert.
They each require a different query to obtain the correct data to insert.

>
> 0. create view v_new_orderlines (theorder, type, stock, line,
> ordercurrent, sellingquant, price, discount, vatrate, comment)
> as SELECT oh.theorder, 'P', '0310', coalesce(ol.line+1,1), 5, 0,
> .52, 0, 0, ''
> FROM orderheader oh
> LEFT OUTER JOIN orderlines ol
> ON oh.theorder = ol.theorder
> WHERE oh.account=' MILN1'
> AND oh.delivery=1
> AND oh.thedate='2002-06-01'
> AND oh.ordertype='O'
> ORDER BY ol.line ;
> 1. COPY v_new_orderlines to '/tmp/new_orderlines.tsv';
> 2. DROP each index from orderlines
> 3. COPY orderlines from '/tmp/new_orderlines.tsv';
> 4. CREATE each index on orderlines
>

JohnT