Migrating an unnormalized db to a new normalized one...

[RickDB]

Hello all...

How do you merge / append records from one database into another? The old database was very poorly designed (i.e. no normalization), but much of the data is the same.

Instinct from what I have learned says do this:

- create a query in each database that joins all fields together into a flat table
- run an update query that migrates the source data to the new database.

I really have no idea though, and to be honest... even if this is how it is done, I am very unsure how to proceed!

All help is very appreciated!

 

[The_Doc_Man]

1. Design your new DB to be more normalized. Add queries in the old DB that would look just like your new DB tables if you opened them.

2. Link BACKWARDS from the new DB to the old one, using append queries to take data from the aforementioned queries in the old DB as you need in order to populate new tables. Yes, query to query. That is to avoid confusion with poorly designed tables in the old DB.

3. Populate "base" tables first. That is, tables that in the new design are not joined to other tables. Then do one-parent child tables next. Be sure to do parents first. Then junction tables.

When you are done, if all relationships and normalization were set up correctly, you should be good to go.

Just an opinion, not to be take as gospel. Also note, I would take this much trouble to do it carefully if this is a one-time thing. If you have a lot of these to do or intend to do it often, my answer might be different.