How to handle data modification in a migration script

avalinavalin Posts: 8
We are planning to start using Source Control in the coming months and are thininkg about how we shall handle data modfications. Static data and data moving when creating new tables can be handled with migrations scripts as we understand. But what if we need to change data in a column without modifying the tables structure? As far as we know a migration script can only be comitted when we change the database objects somehow?

Comments

  • Thanks for your question.

    Have you added this static data to source control or are you maintaining this outside of the tool?

    Are you asking about how to generate a deployment script using SQL Compare Pro that contains both your schema and data changes? At the moment you need to use SQL Compare Pro to generate the schema script and SQL Data Compare Pro to create the data script.

    David Atkinson
    Red Gate
    David Atkinson
    Product Manager
    Redgate Software
  • For the moment we haven't added it to source control.

    What I mean is how to we handle configuration data that we have in the database that can be altered by the user and there for isnät static data but sometimes we want to change it when a customer upgrade to the nextversion.

    For example. I have Table t1 with column col1. In col1 we want to replace all 'X' with a 'O'. This is the only thing we want to do. Not change any database object.
  • I see. It's a data-related change without a corresponding schema change that is saved in source control.

    There's no explicit support for this in the tool, but it's fairly easy to accommodate. Say you have two changes in your release, for example:

    ALTER PROC
    CREATE TABLE

    All you need to do is to choose where the data change should go. If you want to have it after the ALTER PROC and before the CREATE TABLE, simply create a migration script for the ALTER PROC change and add the data update after the ALTER PROC code. Or, create a migration script for the CREATE TABLE change, and add it before the CREATE TABLE code.

    Does this make sense?

    David
    David Atkinson
    Product Manager
    Redgate Software
  • Yeah it's exactly what we mean :)

    Seems like a good work around and should solve our problem :) Is there any plans to add explicit support for this in the future? Would be a great function.
  • Yes, it something that has been raised before.

    I think the solution would be to allow a migration script to be added between two changes, rather than having to replace an existing change.

    I'm glad the workaround works for you.

    Kind regards,

    David
    David Atkinson
    Product Manager
    Redgate Software
Sign In or Register to comment.