Thanks Ann,
Being of two minds I am pleased that I decided to check with this group. As I am migrating from 1.5 to 2.5 I can not consider savepoint as two production DBs are still 1.5. The process I am trying to achieve is at least annual, and at most very infrequently, that efficiency will be secondary to readability and understandability for my successors. I shall also include a precise of these responses into a comment field; just in case.
Thanks again
David

--- In firebird-support@yahoogroups.com, Ann Harrison <aharrison@...> wrote:
>
> On Wed, Dec 14, 2011 at 3:04 AM, p51b.mustang <p51b.mustang@...> wrote:
>
> >
> > 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.
> > 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.
>
>
> Trust the right side, Luke. Maybe someone has fixed this in a more
> recent version of Firebird, but it's a
> characteristic bug of databases that Jim Starkey writes. Unless
> there's a sort (e.g. order by, distinct,
> grouped) they get records one a time rather than finding all
> qualifying rows first as the standard anticipates.
> Rdb/Eln, InterBase, Firebird, Netfrastructure, Falcon, and, until
> recently NuoDB all go into an infinite loop
> if you have any rows in table A an do something like this:
>
> insert into A select * from A
>
> Retrieving all the rows and data first is really inefficient.
> Retrieving the rows and retaining only the db-keys
> (or equivalent) does the same amount of I/O as retrieving data first,
> but doesn't use as much memory or
> temporary disk space. The solution that NuoDB implemented is to keep
> a sparse bitmap of the db-keys of
> the inserted records and not reinsert them when they come back from
> the select. Note that db-keys are not
> necessarily monotonically increasing, so the query has to run until it
> exhausts the result set, not just stop
> when it finds the first "new" record. And, of course, you can't just
> ignore records created by your transaction,
> or running the query twice wouldn't get the right answer.
>
> It's possible to do the same thing using the information in the
> savepoint undo list, I think, and maybe
> Firebird implemented that after 2.0.
>
> Good luck,
>
> Ann
>