A typical scenario I have just had along these lines, is that I have changed a column type and name, but the new column data is initially generated from that of the old column. so steps must be:
- create new column
- run update script to populate new column
- drop old column
this is fairly typical I imagine, but I'm concerned that SQL Source Control wont be able to help me with this, or indeed SQL Compare
perhaps every checkin could have an optional associated data modification script, tho in this case I'd still have to do this change in 2 seperate checkins, so its not ideal at all.
I'm prepared to write custom SQL, but its how this can be integerated in and performed as part of the deployment script.
if the checkin contained the deletion of the old column, and the addition of the new one, then SSC would allow me to add a data migration script to the checkin. I'd still have to communicate to SSC the relationship between these columns, and when to run th script.