Re: Insert from temporary table

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 ?

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.

--

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.