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 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
At present we have two possible solutions which I can see.
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.