Last year we had imported the database into a visual studio database project.
Subsequent db schema changes (like changing of data type, adding new tables, columns) were made offline in the visual studio database project and then the changes were published back into the production database using the dacpac file.
This approach gave us way to version control (add to source control) the database changes.
Now I am facing a challenge when dealing with a task that involves changing schema and data. The following is the explanation of change needed:
Existing table-
Claims: ClaimID ContractID ClaimTitle ClaimantName ClaimantNI ClaimantAddress ClaimDate SettleDate ClaimAmt SettleAmt
A car accident claim usually meant 1 record (claimant) in the database. The business is changing the process and wants to allow multiple claimants for same claim. So the new structure proposed is:
Claim: ClaimID ContractID ClaimTitle ClaimDate SettleDate ClaimAmt SettleAmt
Matter: MatterID ClaimID ClaimantName ClaimantNI ClaimantAddress
The existing claim table has data. So if I delete the columns (ClaimantName, ClaimantNI, ClaimantAddress) from the table and create a new table for those columns (as per the above schema), then I loose the data.
If not for the visual studio database project approach, then I would have connected to SQL server via SSMS, taken the database backup, create the new Matter table, write sql query to Insert records from the Claim table into the Matter table, delete the 3 columns from the Claim table.
What is the approach to make such changes when working with dacpac file in visual studio database project?
There is possibility to use the pre-deploy and post-deploy scripts. I cannot use pre-deploy as the new table isn't available at that point. And post-deploy script approach won't work as by then the deployment will have removed the columns from the Claim table.