0

I'm importing data from a legacy application (php + mysql) to a rewritten version (sql server 2008 and mvc3). I'm currently in the middle of writing a db synchronization / conversion package.

The mysql data has primary keys but no foreign keys for some reason and because the data is still relational (despite the lack of foreign keys) my conversion package is using IDENTITY_INSERT ON / OFF. Because the source data has no way of enforcing data integrity I would like to perform integrity check after I perform an insert and turn IDENTITY_INSERT to OFF.

Is there a good way to script that action? Preferably on a table level rather than whole db?

Thanks.

Ales Potocnik Hahonina
  • 2,977
  • 2
  • 26
  • 32
  • You could load the data in to staging tables. Check and clean the data before moving it to the "live" tables containing your identity fields. – Tony Oct 24 '11 at 15:06
  • Thanks Tony. That's a good idea too. I just don't want to complicate things for whoever is installing this as it has to be deployed to X number of clients. Performing a integrity check and reporting inconsistencies found will do. I just need to know how to run integrity check. – Ales Potocnik Hahonina Oct 27 '11 at 12:24

1 Answers1

1

Personally I would determine which data does not have integrity before importing into the new schema. A simple WHERE NOT Exists query on each child table (or what shoul have been ddesigned as a child table) will do that. I would not import data that cannot be tied to a parent record. I would however, create an exception table and move those records into it and have some of the users see if they can figure out what the parent data should be.

HLGEM
  • 94,695
  • 15
  • 113
  • 186
  • Thanks for a constructive answer. I've considered it and this approach would be valid if there is a large expected number of inconsistencies. Working with the development version of application and test set of data I haven't actually found any relationship issues created. This integrity check would just be a failsafe. To be perfectly honest (you can call me lazy) I don't want to further complicate things by adding a whole new set of tables for errors and a mechanism to check relationships during migration. Though I'll certainly keep it in mind. – Ales Potocnik Hahonina Oct 24 '11 at 15:03