Table Migration In SSDT

Ed Elliott shows us how to use pre-deployment and post-deployment scripts to migrate table data when the tables change:

You sometimes want to do things like split a table into two or move a column into another table and when you use SSDT or the compare / merge type of deployments it can be hard to migrate the data in a single deploy as you can’t insert the data into a table that doesn’t exist and you can’t drop the data before it has bee migrated. To fix this we can use pre/post deploy scripts in SSDT. The overall process is:

  • Pre-Deploy Script, check for column to be migrated

  • Save data in new table not in SSDT (you could have it in SSDT if you use it for multiple releases etc)

  • Let SSDT drop the column and create the new one – you will need to have the option set allow data loss on incremental deployments

  • In the post-deploy copyw the data to the new table

Using separate migration tables is an interesting solution to an old problem.

Related Posts

Faster Scalar Functions In SQL Server 2019

Brent Ozar looks at improvements the SQL Server team has made to scalar functions in 2019: My database has to be in 2019 compat mode to enable Froid, the function-inlining magic. Run the same query again, and the metrics are wildly different: Runtime: 4 seconds CPU time: 4 seconds Logical reads: 3,247,991 (which still sounds bad, […]

Read More

Uncovering Complexity In SQL Objects

Michael J. Swart helps us uncover hidden complexity in database objects: The other day, Erin Stellato asked a question on twitter about the value of nested SPs. Here’s how I weighed in: I’m not a fan of nested anything. Too much hidden complexity. Code reusability leads to queries that are jack of all trades, master […]

Read More

Categories

January 2015
MTWTFSS
  Nov »
 1234
567891011
12131415161718
19202122232425
262728293031