SQL Change Automation 4

Migration script examples

Example cases

Starting with a blank script

 Splitting a column

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.

Example: splitting a column

  1. Create two new columns.
  2. Split and copy data to new columns.
  3. Drop the original column.
 Merging columns

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.

Example: merging columns

1. Create the new column.

2. Copy data to the new column.

3. Drop the original columns.

 Splitting or merging tables

You can follow the same steps for splitting or merging a tables as you would for splitting or merging columns. 

Splitting a table
123
Create the new table.Migration script to copy the data to the new table.Drop the columns in the original table.
Merging tables
123
Create the new table.Migration script to copy data from the old tables to the new table.Drop the original tables.
 Adding a NOT NULL constraint to a column

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:

Example: adding a NOT NULL constraint to a column

  1.  Update NULL entries with NOT NULL values.
  2.  Add the NOT NULL constraint to the column.
 Changing the data type or size of a 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

 Renaming a table

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 DROP and CREATE statements in the generated script with the sp_rename stored procedure.

Tutorials


Didn't find what you were looking for?