Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 ?

share|improve this question

migrated from programmers.stackexchange.com Jul 8 '13 at 23:30

This question came from our site for professional programmers interested in conceptual questions about software development.

2 Answers 2

up vote 6 down vote accepted

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.

share|improve this answer
    
+1 for the real-life answer. But, how do you checkwhich data is new ? Don't you have to compare business keys ? –  user1598390 Jul 8 '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. –  waxwing Jul 8 '13 at 18:16
    
Of couse, your approach will be of help. –  user1598390 Jul 8 '13 at 18:17

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.

share|improve this answer
    
You mean versioning whole plain text exports of the database in a version control system ? –  user1598390 Jul 8 '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 8 '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 ? –  user1598390 Jul 8 '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 8 '13 at 21:26

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.