Importing from one database to another, with confllicting ID fields

Question

I have a Ruby on Rails application that has two active environments, Stage and Production. Our development team has been using Stage, but we would like to move our data onto the production server for various reasons. However, there are conflicting ID's in the new database, so it's not as simple as pulling the data from one location and inserting it into another. For example, say we have a table called Widgets:

Widget:
  id: 9836
  name: "Staging widget"
  parent_id: 9635
  container_id: 533

If the above data is one of our widgets, we can't do the import because there is already a widget with ID 9836 and/or there is already a container with the ID 533, meaning we would need to crawl the association chain to construct new containers before putting widgets in them.

We are using MySQL databases for both environments. I thought about doing the import, and just adding 10,000 to all the appropriate columns that end in _id, as it would push us beyond the conflicting boundaries, but that seems like a bad solution.

Are there any tools, projects, or ideas that may help me solve this problem?

Answer 1

In the past I have solved this problem in a kludgy way by making all of the IDs negative for the data I am importing. This only works if your ID column is signed, and there are only two data sources at most, of course.

Answer 2

Well, what you should do (I am assuming this is MySQL based on the tag) is to run the following SQL (I am not a RoRails guy, but a PHP w/ MySQL, so I apolgize if this doesnt apply):

This post id one basic idea

The other option is to use a program like the phpMyAdmin (yes, it is php) which is essentially a database GUI tool. With phpMyAdmin you can export select fields of the table. Just export all the data EXCEPT the ID field and then you can just run an import of that data.

This part I am a little fuzzy on. I think you have an actual file with the Widget stuff in it. If so, create a script that goes through each row of data and makes the Widget file.

Answer 3

If you can't use negative ids simply add a number to all imported ids. The number has to be greater than maximum id from the destination database.