On 14/12/11 08:04, p51b.mustang wrote:
> Hi,
> I am seeking advice on whether this is possible:-

> ...
> In other words a table inserting into itself. Essentially for a particular sitecode the records are duplicated for each sitecode relationship.
>

> On the left side of my brain, I say firebird would collect all records from the select first and then perform inserts with no problems.
That's what Oracle does.

> On the right side, I say as records are inserted these very same records my be gathered into the select and also be duplicated with horrifying results.
That's what Firebird does!


In Oracle I frequently use "insert into table_a select * from table_a"
to build up some volume in a table. The first time I tried that in
Firebird, it never finished - I had to kill it.

Oracle's Read Consistency works differently from Firebird's. When you
SELECT from a table in Oracle, it gives you the rows exactly as they
were at the time you issued the SELECT.

So, your table has 200 rows in it, then the insert inserts 200 rows. Run
it again and yo get an extra 400 rows, then 800 and so on.

Firebird's read consistency doesn't seem to do this, you get to see all
the rows in the table even the ones being inserted by the command.

Best avoided!

By the way, I've not tried this in Firebird 2.5 as I don't want to have
to kill off my session to stop it running away!

HTH

Cheers,
Norm.

--
Norman Dunbar
Dunbar IT Consultants Ltd

Registered address:
Thorpe House
61 Richardshaw Lane
Pudsey
West Yorkshire
United Kingdom
LS28 7EL

Company Number: 05132767