With some periodicity this needs to be done:
- Inserting data from a production database into a test/development database, so that programmers have fresh data to test on.
- Migrating data from a database into another, for whatever reason.
Note: databases are relational.
The problem is:
- Databases were modeled with a "all-PK-are-surrogate" policy.
- Moving data from a database to another involves programming to avoid colision of PK values ( generated by sequences ).
- A table pairing the values of the PKs of origin and destination database's tables is needed in order to do the "equivalency" between surrogate keys.
- Said table has to be created in advance of migration, by matching business keys (which are not PK, so don't govern FKs)
- Data migration even of a single table in not trivial, as opossed to tables with natural keys.
What's the easiest way of copying rows from one database to another when all tables have surrogates keys ?