Alter instead of drop and recreate

Cat_Bill

Is there a way to force SQL Change Automation to use alter instead of drop and recreate when renaming a table? I don't want to lose the data in the table.

way0utwest

Not directly. The tools work by looking for changes, and there isn't a way to detect that this is a rename v a drop and add, at least not easily. When you import the migration script, that is the time to change this. I'd take this script:

And edit it to be this:

As a side note, renaming objects is an anti-pattern that you should avoid where possible. That being said, it is necessary sometimes. While SQL Prompt has a smart rename to find references and ensure things are renamed, for tables/columns, you need to manually fix the migration script.

way0utwest

Is this SQL Change Automation project  in VS/SSMS or are you starting in a SQL Source Control project?

Cat_Bill

SSMS