Normalizing a table into two tables while preserving data
element533
Posts: 6
Hello,
I'm evaluating SQL Compare, and I'd like to see if it's possible to do the following.
TRIVIAL EXAMPLE:
Assume I am starting with an existing table:
Customers
=======
Id
Name
Address1
Address2
City
State
Zip
and I want to normalize this by breaking it into two tables:
Customers
=======
Id
Name
AddressId
Addresses
=======
Id
Address1
Address2
City
State
Zip
I want a script that will create the new table, migrate the appropriate data, and establish a foreign key relationship.
* Can SQL Compare produce such a script automatically?
* If not, I am comfortable writing such a script myself. But in this case, how does SQL Compare incorporate my manual scripts into its knowledge of how the schema is being changed?
The end goal is to use SQL Source and SQL Compare to fully or nearly fully manage source control and versioning. There may be places where we have to manually author migration/upgrade scripts, but we'd like to have these scripts be somehow accounted for in the comparison/versioning process.
I'm evaluating SQL Compare, and I'd like to see if it's possible to do the following.
TRIVIAL EXAMPLE:
Assume I am starting with an existing table:
Customers
=======
Id
Name
Address1
Address2
City
State
Zip
and I want to normalize this by breaking it into two tables:
Customers
=======
Id
Name
AddressId
Addresses
=======
Id
Address1
Address2
City
State
Zip
I want a script that will create the new table, migrate the appropriate data, and establish a foreign key relationship.
* Can SQL Compare produce such a script automatically?
* If not, I am comfortable writing such a script myself. But in this case, how does SQL Compare incorporate my manual scripts into its knowledge of how the schema is being changed?
The end goal is to use SQL Source and SQL Compare to fully or nearly fully manage source control and versioning. There may be places where we have to manually author migration/upgrade scripts, but we'd like to have these scripts be somehow accounted for in the comparison/versioning process.
Comments
Thanks for your questions.
The most appropriate product to auto-generate a split table script is SQL Prompt Pro, which has a Split Table feature (right click on the table in the Object Explorer) .
Your second question is about how to incorporate this knowledge so that SQL Compare will re-use it. This isn't yet possible in SQL Compare 9 and SQL Source Control 2, but will be possible in SQL Compare 9.5 and SQL Source Control 3 due to be released later this year.
Can I ask why you need these custom scripts to be 'accounted for' in your versioning process? Is there any reason why you can't generate the script with SQL Compare and simply hand-modify the script to your liking before saving it off?
Kind regards
David Atkinson
Product Manager
Red Gate Software
Product Manager
Redgate Software
Thanks for your response.
That would certainly be an acceptable solution, but where does that leave us in regards to source/version management?
For example, if Developer A writes such a custom script, executes it against his working database, and commits the schema to source control using SQL Source, and then Developer B updates his working database using SQL Source, what happens? Unless SQL Source "knows" that the custom script is required when updating from rev X to rev Y, it will just send down some drop column and create table statements and be done with it, correct? That would blow away Dev B's address data, plus it would fail if there were a NOT NULL foreign key constraint on AddressId (because there would be no rows in the Addresses table).
Does Dev A have to check in the custom script by hand? If so, where? How does that get communicated to Dev B? How does it get communicated to Dev C who isn't sure what version of the database he already has? We want a system or tool that can help manage these questions for us, so we can spend our time engineering the data model instead of engineering change control processes.
This is not merely a development issue. It also concerns production releases. If we have to manually manage a library of custom scripts and manually ensure that they are executed in the right order and only once, the benefit of using SQL Source to manage our schema starts to fall away. We end up having to still do what we do today (check in custom scripts and manage their deployment by hand).
We are comfortable *writing* scripts by hand, but we are really looking for help in terms of managing and versioning them in the context of the DB schema as a whole. Schema versioning sometimes includes orchestrated or strategically designed DML and data migration steps; it is not simply a point-to-point diff of structures, relationships, and constraints.
Does that make sense? I know I'm probably preaching to the choir here...
What features are coming in the new versions of your products that you think address this?
Thanks,
Steven
Does this sound like this should meet your requirements?
David
Product Manager
Redgate Software
David
Product Manager
Redgate Software
Is there an accurate release date for this now - I am assuming tomorrow or the day after if it is Q3
Seriously though this feature is one I really need and the ability to run before and after scripts as part of a migration was exceptionally useful in DBGhost.
This lets you replace built in behavior for a given migration transition. It doesn't let you put a pre/post-deployment script for the entire script.
What problem are you hoping this feature will fix for you?
David Atkinson
Product Manager
Red Gate Software
Product Manager
Redgate Software
SVN
I want the ability to execute custom scripts as part of a developer/build process. So if we need to pre-process data, or apply the schema change ahead of the synchronisation in order to preserve or migrate data, then have the ability to execute that as a pre compare step.
For more information visit:
http://www.red-gate.com/MessageBoard/vi ... 1312#51312
Let us know if this doesn't meet your expectations.
David Atkinson
Product Manager
Red Gate Software
Product Manager
Redgate Software