Subject Re: [firebird-support] insert into tab1 (...) select (...) from tab1
Author Helen Borrie
At 05:48 PM 15/09/2009, you wrote:
>I sometimes need to duplicate data in a table. I use
>"insert into tab1 (...) select (...) from tab1" syntax.
>The problem is, that it result in infinite loop, so it selects records that have been just inserted and inserts it again and so on.
>Is it correct behaviour?

From the standards POV, no, it is not correct behaviour. However, it *is* how it is in Firebird and InterBase - a "known Issue".

>I expected, that select takes a snapshot of table content and inserts it once.

No, that's not how it works. It is running through a cursor, reading one row at a time and writing the result. If you don't put a "stopper" on it (as indeed you have done) then it will keep reading the new rows as it finds them.

>My temporary solution is to add temp_field, fill existing records with some value, and add "where temp_field is null" to select clause.

It's a correct solution. Just make sure that, if you run this query repetitively, perform some kind of update subsequently, to make the "stopper" field non null.