SQL Change Automation 4

When you split a column in your development database, this is interpreted as creating two new columns and dropping the original one. This approach results in the data in the first column being lost.

You can avoid this data loss by creating a migration script to copy the data from the original column to the two new columns before it is dropped.

In this example, we'll split a column in our development database into two columns, and use a migration script to copy and preserve the data.

The process is split into three commits:

Example: splitting a column

1. Schema change
Create two new columns

Commit

2. Migration script
Split and copy data to the new columns

Commit

3. Schema change
Drop the original column

Commit

1. Set up the databases

This script creates two identical databases: talkr_dev (our development environment) and talkr_prod (our production environment). Both contain a table named customers with some data.

If these databases already exist on your server, executing this script will drop and create them again.

SQL creation script

USE master
GO
IF DB_ID('talkr_dev') IS NOT NULL
BEGIN
     ALTER DATABASE talkr_dev SET SINGLE_USER WITH ROLLBACK IMMEDIATE
     DROP DATABASE talkr_dev
END
GO
IF DB_ID('talkr_prod') IS NOT NULL
BEGIN
     ALTER DATABASE talkr_prod SET SINGLE_USER WITH ROLLBACK IMMEDIATE
     DROP DATABASE talkr_prod
END
GO
 
CREATE DATABASE talkr_dev
GO
USE talkr_dev
GO
CREATE TABLE [dbo].[customers] (
        title varchar(10),
        name NCHAR(50),
        city varchar(25))
GO
INSERT INTO customers ([title],[name],[city]) VALUES ('Mr', 'Miles Davis', 'Santa Monica')
INSERT INTO customers ([title],[name],[city]) VALUES ('Mrs', 'Ella Fitzgerald', 'Beverly Hills')
INSERT INTO customers ([title],[name],[city]) VALUES ('Mr', 'Glenn Miller', 'Bedford')
INSERT INTO customers ([title],[name],[city]) VALUES ('Master', 'Benjamin Clementine', 'London')
GO
 
CREATE DATABASE talkr_prod
GO
USE talkr_prod
GO
CREATE TABLE [dbo].[customers] (
		title varchar(10),
		name NCHAR(50),
		city varchar(25))
GO
INSERT INTO customers ([title],[name],[city]) VALUES ('Mr', 'Miles Davis', 'Santa Monica')
INSERT INTO customers ([title],[name],[city]) VALUES ('Mrs', 'Ella Fitzgerald', 'Beverly Hills')
INSERT INTO customers ([title],[name],[city]) VALUES ('Mr', 'Glenn Miller', 'Bedford')
INSERT INTO customers ([title],[name],[city]) VALUES ('Master', 'Benjamin Clementine', 'London')
GO

Run the script on your server and refresh the Object Explorer.
The talkr_dev and talkr_prod databases are created.
Configure talkr_dev as your development database.

2. The migration script to split a column

The autogenerated migration script will need to be replaced with this SQL:

This script will:

Create the new columns. We need to create two new columns, first_name and last_name, on the talkr_dev database:
Copy the existing data to those columns.
Remove the old column. We can safely remove the original name column.

Add first_name and last_name

ALTER TABLE customers ADD first_name VARCHAR(25) NULL, last_name VARCHAR(25) NULL

UPDATE customers SET first_name = SUBSTRING(name, 1, CHARINDEX(' ', name) - 1),

                     last_name  = SUBSTRING(name,    CHARINDEX(' ', name) + 1, LEN(name))


ALTER TABLE customers ALTER COLUMN first_name VARCHAR(25) NOT NULL

ALTER TABLE customers ALTER COLUMN last_name  VARCHAR(25) NOT NULL

ALTER TABLE customers DROP COLUMN name

GO

SQL Change Automation 4

Splitting a column without data loss

Example: splitting a column

1. Set up the databases

2. The migration script to split a column