How to handle data modification in a migration script

avalin · January 20, 2012 4:08AM

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?

David Atkinson · January 20, 2012 7:02AM

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

avalin · January 20, 2012 8:12AM

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.

David Atkinson · January 20, 2012 8:42AM

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

avalin · January 23, 2012 1:47AM

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.

David Atkinson · January 23, 2012 4:02AM

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

How to handle data modification in a migration script

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk