Migration script examples
Published 31 January 2020
Starting with a blank script
From a schema point of view, when you split a column you actually create two new columns and drop the original one. If you deploy this change, any data in the dropped column will be lost.
To avoid this, you can write a migration script to copy the data to the new columns before dropping the original column. For a walkthrough of this process, see Splitting a column without data loss.
From a schema point of view, when you merge a column, you create a new column and drop the original columns. If you deploy this change, any data in the dropped columns will be lost.
To avoid this, you can write a migration script to copy the data from the original columns to the new column before dropping the original columns.
You can follow the same steps for splitting or merging a tables as you would for splitting or merging columns.
Splitting a table
|Create the new table.||Migration script to copy the data to the new table.||Drop the columns in the original table.|
|Create the new table.||Migration script to copy data from the old tables to the new table.||Drop the original tables.|
If you add a
NOT NULL constraint to a column in a table that contains
NULL entries, without a default value, the deployment will fail.
Rather than adding a default value, you can write a migration script to update all the existing
NULL entries with a
NOT NULL value before adding the
NOT NULL constraint to the column:
When you change a column's data type, data might be lost. For example, if the data type you change it to doesn't accommodate some of the rows, data will be truncated during deployment.
To avoid this, you can create a migration script to appropriately modify rows that would otherwise be truncated.
Replacing uncommitted schema changes
When you rename a table in SQL Server Management Studio, this is interpreted by SQL Change Automation as dropping and recreating the table. If the table contains data, the data will be lost.
To avoid this, generate a migration script for this change and replace the
CREATE statements in the generated script with the
sp_rename stored procedure.