3

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 ?

Tulains Córdova
  • 713
  • 6
  • 13
  • 27

2 Answers2

7

We came across this problem (or at least a similar one) not long ago at work, and our solution is to divide the migration process into steps:

  • Insert the new data to a temporary staging table(s)
  • Update the surrogate keys in the staging table(s) so that there are no conflicts
  • Insert the data into the target table(s)
  • Delete the staging table(s)

The second part is the tricky part, but in our case we were able to follow this algorithm for each table:

  • If there are any foreign keys against this table, set up foreign keys in the staging table with ON UPDATE CASCADE
  • Update the primary keys of all rows to a new id range. We used negative ids. E.g. UPDATE STAGINGTABLE SET ID=-ID-1; Now there is no risk of id clashes in the next step.
  • Now, for each row in STAGINGTABLE, generate and assign a new id

Repeat for each staging table. When all staging tables are processed, INSERT the data into the target tables.

One assumption this relies on is that all data is new and independent from the data already in the database.

waxwing
  • 196
  • 3
  • +1 for the real-life answer. But, how do you checkwhich data is new ? Don't you have to compare business keys ? – Tulains Córdova Jul 08 '13 at 18:01
  • Our case was easier that way - we knew that all data was new. I haven't thought about the more difficult case you describe, but I think it's possible to borrow ideas from the approach we used. –  Jul 08 '13 at 18:16
  • Of couse, your approach will be of help. – Tulains Córdova Jul 08 '13 at 18:17
-1

Alternative approach if you do not need to disguise the data for privacy reasons. All database changes and values for lookup tables should be in source control. We simply restore the last prod backup and then use source control scripts to add whatever dev changes haven't made it to Prod yet. If you have multiple databases, all should be refreshed at the same time. One advantage of this is that you have roughly the same number of records as prod which makes a huge differnce in testing your database code for performance. It also helps keep your devs using source control for changes and helps practice deployments before you move them to other environments.

HLGEM
  • 3,133
  • 17
  • 18
  • You mean versioning whole plain text exports of the database in a version control system ? – Tulains Córdova Jul 08 '13 at 19:17
  • Every object and every change to an object in a database should be in source control. You should be able to run the scripts in order by version. – HLGEM Jul 08 '13 at 21:04
  • I meant data. Not only structure: You mean versioning whole plain text exports of the database (structure and data) in a version control system ? – Tulains Córdova Jul 08 '13 at 21:08
  • The only data that should be in source control would be lookup tables that are not ever changed from the application. – HLGEM Jul 08 '13 at 21:26