Re: Move rows from one database to other

Thomas:

On Wed, Feb 22, 2017 at 1:51 PM, Thomas Güttler
<guettliml(at)thomas-guettler(dot)de> wrote:
> I want to **move** the data. The data should get deleted on the satellite
> after transfer.
> I don't know how to delete the data which was copied, since inserts can
> happen during the copy statement.

Depending on the structure / control / kind of ops you have on the
satellite you can try a two-table trick, similar to a common one used
to process files in spool directories.

1st, you move everything to a holding table transactionally in the
satellite ( insert into holding table delete from main table returning
* )

Then you have the holding table to transfer and clear, not touched by
anyone ( as transfer process is the only one touching it )

You can do some tricks. You could inherit holding from main if you
want to be able to see data while it is been transferred. Or play
renaming tricks. It all depends in the concrete app, but you can try
to fit the pattern in it, I've done it several times and its a useful
one.

Francisco Olarte.