Re: Insert from temporary table

In article <966675625.11839.0.nnrp-07.9e984b29_at_news.demon.co.uk>,   "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote:
>
> Can you clarify a couple of points -
>
> Is this using 8.1 with 'create global temporary table' ?
> Are the GTTs created with 'on commit preserve' or
> 'on commit delete' rows ?

Yes, it is Oracle 8.1.6 and the tables are created with 'on commit delete'.

> Your two descriptions are slightly different - with
> GTTs you appear to do just one commit at the end
> of all the processing; with the normal tables you
> 'truncate after every commit' - i.e. implying you
> do more that one commit - excess commits
> with GTTs could be why you 'lose' rows.

Sorry for the confusion. Yes, I do more than 1 commit but ...

I have two different global temporary tables. So I fill up the first temporary table, select from that first temporary table and insert those rows in the first destination table. Then I do a commit which should remove all the records from the temporary tables. After the commit the second temporary table is filled up, select on that second temporary table and insert the results into second destination table. So here I do a second commit.

I changed the temporary tables to 'normal' tables and placed a TRUNCATE TABLE before the two commits. I think this approach is identical to the first one. Here I get the desired results.

Maybe I should only use 1 commit with temporary tables ?

Greetings,

Tom at Vanstraelen . net

> --
>
> Jonathan Lewis
> Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk
>
> straelet_at_my-deja.com wrote in message <8ngvju$vr0$1_at_nnrp1.deja.com>...
> >Hi all,
> >
> >I'm working on a DWH project and I have to move data from table A to
> >table B while doing some lookups on the data. I use a temporary table
> >to put all the records in from A and insert data into table B by
 doing
> >a SELECT GROUP BY on the temporary table so that all the data is kind
> >of compressed (many records have the same keys). Finally I commit so
> >that the data in the temporary table is deleted.
> >
> >This proces is repeated a few times, every time another set of
 records
> >is moved from table A to table B.
> >
> >The problem is that the data inserted in table B is wrong, the amount
> >of records is almost right, but a few records are missing.
> >
> >If I change the temporary tables to normal tables and I add a
 TRUNCATE
> >TABLE in my code whenever I COMMIT, the numbers of records inserted
 in
> >table B are right.
> >
> >Can it be that the data in the temporary table is still being deleted
> >while I already insert new data into it ? Or is there something else
 I
> >should know when using temporary tables ?
> >
> >Kind Regards,
> >
> >Tom at Vanstraelen . net
> >
> >
> >Sent via Deja.com http://www.deja.com/
> >Before you buy.
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Aug 23 2000 - 07:34:00 CDT