Normalizing a table into two tables while preserving data

element533element533 Posts: 6
edited October 17, 2011 5:26PM in SQL Compare Previous Versions
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.

Comments

  • Hi,
    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
    David Atkinson
    Product Manager
    Redgate Software
  • Hi David,

    Thanks for your response.
    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?

    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
  • Developer A would need to check in the custom script to a specific repository in source control. Both SQL Source Control AND SQL Compare will know to re-use these scripts if a 'Get Latest' or a deployment requires them. So Developer B and C don't need to know about the custom script; it will just work.

    Does this sound like this should meet your requirements?

    David
    David Atkinson
    Product Manager
    Redgate Software
  • That sounds exactly like something that would help with this scenario... That is the new feature that's supported in the upcoming version of the tools?
  • That's right. This functionality will be the principal new feature in the next release of SQL Source Control and SQL Compare.

    David
    David Atkinson
    Product Manager
    Redgate Software
  • That's right. This functionality will be the principal new feature in the next release of SQL Source Control and SQL Compare.

    David

    Is there an accurate release date for this now - I am assuming tomorrow or the day after if it is Q3 :D

    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.
  • We're hoping for an early access build next week so it's not far off. Can I ask which source control system you're using? This version will work for TFS, SVN and Vault only.

    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
    David Atkinson
    Product Manager
    Redgate Software
  • We're hoping for an early access build next week so it's not far off. Can I ask which source control system you're using? This version will work for TFS, SVN and Vault only.

    What problem are you hoping this feature will fix for you?

    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.
  • SQL Compare and SQL Source Control can now work side by side to achieve what you describe.

    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
    David Atkinson
    Product Manager
    Redgate Software
Sign In or Register to comment.