Data migration scripts

Kennethtruyers

I'm having some trouble for a few use cases that we have.

When we do a change to our database schema often we have to migrate some data with it. I looked into migration scripts but couldn't really find what we needed. The migration scripts all run before the schema compare which often defeats the purpose.

Example:

I have a table customers and a table addresses. A customer has an AddressId. Now I want to reverse the relation and have a customer id on the address.
In that case I add a column CustomerId to the Address table and let schema compare take care of the creation.

However, now I want to write a script that reverses the relation, but I can't do that because it will run before schema compare runs.
I could write the whole migration in the script (adding of the column and then migrating the data), but that kind of defeats the purpose of having the automatic compare option.

I much rather have the schema compare figure out the differences and then have a script that moves the data.

How would you do this?

Anu D

Hi,

Thanks for your post.

In SQL Compare you have an command line option to include "Static Data" provided you are using Source control or script folder as source.

More about it here in this article.

If you are using Database as a source then you unfortunately have no option in SQL Compare but can use SQL Data Compare to migrate the data. More about SQL Data Compare here.

You also can automate and schedule this comparison process. Please let me know if you need further information.

Kennethtruyers

I looked into these articles, but none of them seem to solve the issue I describe in the post.

This is just an example issue, but having an andwer for this particular one would be very helpful in solving other edge cases we might have.

Thanks