How to handle data modification in a migration script
avalin
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
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
Product Manager
Redgate Software
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.
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
Product Manager
Redgate Software
Seems like a good work around and should solve our problem
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
Product Manager
Redgate Software