Let us assume that I want to normalize a table into 2 tables. Eg. A table Person with columns PhoneNumber1...PhoneNumber5 into tables Person and PhoneNumbers with foreign key constraint from Person into PhoneNumber and deletion of PhoneNumber1..PhoneNumber5 columns from the Person table. I want to preserve data ie PhoneNumber1..PhoneNumber5 should end up as one record each for non-null data in the PhoneNumber table . Can any schema comparison tool help achieve this ? I checked SSDT and it does not support it even though this "feature" is on their "roadmap"
This is just one simple scenario that I face in my day to day life and we have been using hand-coded SQL to manage this. In a more complicated scenario, one set of tables could change into another set of tables. Is there an easy way in any tool to manage these kind of schema changes and at the same time manage the data mappings successfully ?
This goes far beyond refactoring in any tool of which I'm aware. SSDT helps you manage and control your schema, but you'll need to handle changes like this through careful planning and handling to avoid data loss.
If you were creating a brand new database to house the new structure/data, that might be different. However as you're trying to do this by renaming/redesigning existing tables, you'll need to be very detailed in your plan and handle it carefully.
You could do this through SSDT, but I'd use multiple versions of the product to do it: 1. Create new table with the new structure. 2. Copy data from original to new - requires a custom script. 3. Refactor original table to something like "Name_Backup" 4. Refactor new table to the desired name.
You could likely do steps 1 & 2 in one project and possibly steps 3 & 4 in another version of the project. However you go about it, this sort of refactoring requires more planning and manual work than the more straightforward refactoring of just renaming a column or adding/removing objects.