Adding NOT NULL columns to tables with data

LittleColinLittleColin Posts: 16
edited October 17, 2011 6:46PM in SQL Compare Previous Versions
Newbie question:

Can SQL Compare support the following for static data tables (by static I mean that the source database contains a superset of the data that is in the destination database and the source is the master of this data):
* Spot that a NOT NULL column has been added to the source
* Create it as a NULL allowed column in destination prior to data migration
* Include the data migration script (e.g. from SQL Data Compare)
* Change the NULL columns created above to the desired NOT NULL
--
http://www.cybercohesion.com
· Share on Twitter

Comments

  • David AtkinsonDavid Atkinson Posts: 1,458 Rose Gold 2
    No, it can't do this, but it's a problem we're aware of and it's very high on our list of candidates to be included in a later release.

    Your last step of converting the column back to NOT NULL would surely fail as any pre-existing records in the target will have NULL in the new column and this is incompatible with changing it to NOT NULL.

    The solution would have to be to allow the user to specify a default value for existing records in the target.

    David Atkinson
    Product Manager
    Red Gate Software
    David Atkinson
    Product Manager
    Redgate Software
    · Share on Twitter
  • LittleColinLittleColin Posts: 16
    The last step should work because the preceding data migration step would have populated the columns with data. This can be guaranteed because the source data has already got the NOT NULL constraint so must have values for the additional column.

    (note that I haven't yet tried the SQL Data Compare to see if it can add data for the added columns but am assuming it can - as opposed to only working on a row added or removed basis - or am I wrong about that?)

    But thanks for your reply - am looking forward to this feature being added.
    --
    http://www.cybercohesion.com
    · Share on Twitter
  • David AtkinsonDavid Atkinson Posts: 1,458 Rose Gold 2
    Ah - I think I see what you mean. SQL Data Compare would have to be used to move the Data and if the schemas are different it wouldn't be able to deal with the additional column.

    Therefore the schemas would need to be synched first in SQL Compare, which is not as data-aware as it could be so it will complain about the NOT NULL column. Ideally it should read the source table and spot that values for these additional columns already exist and use these.

    In the meantime your best bet is to temporarily change the source column to be NULL, synch the schema using SQL Compare, synch the data using SQL Data Compare and set the source column back to NOT NULL. It's not as smooth as it should but hopefully we can improve matters in a later release.

    Thanks for bringing this to our attention,

    David Atkinson
    Red Gate Software
    David Atkinson
    Product Manager
    Redgate Software
    · Share on Twitter
  • David AtkinsonDavid Atkinson Posts: 1,458 Rose Gold 2
    SQL Compare has a new feature that works in conjunction with SQL Source Control. SQL migrations allows the user to add a custom script that overrides default behavior.

    http://www.red-gate.com/MessageBoard/vi ... 1312#51312

    Let us know whether this meets your expectations.

    David Atkinson
    Red Gate Software
    David Atkinson
    Product Manager
    Redgate Software
    · Share on Twitter
Sign In or Register to comment.