I'm focusing on a pre-existing MS SQL Server database which was not designed perfectly. Indexes and primary secrets were missing generally. There have been NO foreign key constraints.
I designed a local copy from the DB (so they won't focus on the live version), and glued the majority of the problems. I would now like to syncronize my changes towards the production server,
Because there have been no previous foreign key constraints you will find key mismatches within the tables, 1000's of these. I've attempted a couple of synchronization tools, but not one of them allows to disregard or resolve FK mismatches. (in SQL Server Management Studio it's known as "Check Existing Data On Creation Or Re-Enabling: No")
It is possible to method to auto-resolve the discrepancies from the old database?
Use SQL DATA COMPARE from red-colored-gate to syncrhonize data.
You may also try SQL rival syncrhonize structure, before synchronize data if SQL data compare aren't effective.
Exactly what do you mean by "auto-resolve"? Existing information is "bad" - that's, the restrictions you are attempting to impose are violated. There's not a way to create your computer data "good" without modifying it. And there's clearly not a way to determine instantly how you can fix the information.
So, the very best factor you could do this would be to evaluate the information, discover how you can correct it, perform the corrections by hand, after which add the restrictions. You might just remove all of the sporadic rows (most likely an awful idea, if you want the information), or pressure the server to disregard the restrictions for existing data (certainly an awful idea).
If you want to drop the sporadic data, I'd suggest you to definitely write (or generate, if there are plenty of foreing secrets) SQL scripts such as this:
DELETE a FROM a LEFT JOIN b ON a.b_id = b.id WHERE b.id IS NULL ALTER TABLE a ADD CONSTRAINT FK_a_b_id FOREIGN KEY (b_id) REFERENCES b (id)