We currently use Database projects during automated builds to ensure our test and development databases are in sync with source code.
During the publish profile, we have noticed a problem which isn't totally covered by the workflow we're currently using and are curious the best way to manage this.
We have a re-working of the database schema and decide that a column (name) from a table (user) should be moved to a new table (userdetail).
The new userdetail table, requires the existing content of name column on table user, but we also want to drop the column name from table user. To ensure our database project is up to date.
The problem is that if we try to populate the userdetail table in pre-deployment, the userdetail table doesn't exist. We can create the table in pre-deploy but then the db project tries to create it again during deployment and fails.
If we try to populate userdetail post deployment, the name column has been dropped and we can't use this to populate userdetail.
At present we have two possible solutions which I can see.
Leave the name column in the user table, which means the population of userdetails runs ok, but we're left with the column needing to be removed manually post deployment, which is prone to error.
Create a temporary table to capture the information from user during pre-deployment and store it for use during a post-deployment script.
Drop the temporary table during post deployment.
Option 2 seems the best at the moment, but it's a pain to have to manually store and use data later in this way. Is there a recommended method to cater for this eventuality?
I think your best bet is going to be option #2. The "DROP" statement would be generated when you run the publish command. That wouldn't take into account any pre or post deploy scripts so the column would be dropped before you had a chance to run your conversion script. You really only have the option of doing this in two different builds or creating some sort of staging table pre-deploy and dropping it post-deploy.
