When you split a column in your development database, SQL Source Control interprets this 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:

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.

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

1. Run the script on your server and refresh the Object Explorer. 
   The talkr_dev and talkr_prod databases are created.

2. Link talkr_dev to a new evaluation repository. 
   If you previously linked either of these databases to source control, you'll need to unlink them before continuing.
   

3. Now we need to do an initial commit of all the objects in the talkr_dev database. With talkr_dev selected, go to the Commit tab. 
   The customers table is listed as a new change to commit:

   
4. Add a comment and click Commit.
   The customers table is committed to source control.

2. Create the new columns

Next, we need to create two new columns, first_name and last_name, on the talkr_dev database:

1. In the Object Explorer, right-click talkr_dev and select New Query. 

2. Paste this SQL into the new query window:

   Add first_name and last_name

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

3. Execute the script.

4. To check the new columns exist, open another new query and execute this SQL:

   SELECT * from dbo.customers
   GO

    The results window displays the new first_name, last_name columns:

   
5. Commit the changes to source control.

We're now ready to copy the data to the first_name and last_name columns. The new columns currently support NULL entries; this is because they don't contain any data yet.

3. Copy the existing data

Now we've committed the new columns to the talkr_dev repository, we need to write a migration script to copy the existing data to those columns.

1. In the talkr_dev SQL Source Control window, navigate to the Migrations tab.
   

2. Under Create new migration script, select Create blank script.
   

   We're starting with a blank script here because we're only using the migration script to handle our data changes. We're making schema changes in separate commits, which will deploy as normal. This is a data-only migration, and is considered best practice in most cases.

3. In the Name field, give the script a name (eg Copy name data to new columns).

4. In the editor window, paste this SQL:

   UPDATE customers SET first_name = SUBSTRING(name, 1, CHARINDEX(' ', name) - 1),
   
                        last_name  = SUBSTRING(name,    CHARINDEX(' ', name) + 1, LEN(name))
   
   GO

   This script splits the values in the name column where the space occurs, and copies the resulting strings to the first_name and last_name columns respectively.

5. Execute the script. 
   We want to make sure the migration script runs successfully before deploying to a production environment. While working in our development environment, it's safe to edit and run scripts multiple times before committing.

6. Click Save & Close.
7. Commit the changes to source control.
   

4. Remove the old column

Now we've written the migration script to copy the data to the new columns, we can safely remove the original name column.

1. In the Object Explorer, right-click talkr_dev and select New Query. 

2. Paste this SQL into the new query window:

   DROP name column

   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

3. Execute the script.

4. Open another new query window and execute the following SQL:

   SELECT * from dbo.customers
   GO

5. Check the Results tab to confirm that the name column no longer exists.

   

6. Commit the changes to source control.

   At this stage, SQL Source Control will warn you about data loss; this is normal when performing a data-only migration. We've already executed our migration script to copy the data to the two new columns, so no data loss will occur.

5. Deploy the changes to production

In this example, we'll use SQL Compare to deploy a source control revision directly to our production database. It's possible to use Get latest in SQL Source Control to deploy these changes, however we don't recommend linking your production database directly to source control.

1. Open SQL Compare.
2. In the New Project window: 
   1. Set Source to Source Control.
   2. Set Target to Database.
   3. Point the Source repository at the evaluation repository created in step 1.2 (eg talkr_dev).
   4. Point the target server and database at talkr_prod.
   
3. Click Compare Now. 
   SQL Compare highlights the differences in the customers table between talkr_dev and talkr_prod.
4. Use the checkbox to select the change to the customers table.
   
5. Click Deploy...
6. On the Deployment method page, select Deploy using SQL Compare. Click Next.
7. On the Review Deployment Script page, check the following tabs:
   1. Summary - make sure your migration script is listed as part of the deployment.
      

   2. Warning - review any warnings that aren't covered by your migration script.

      How are these warnings covered by the migration script?

      • No default value for column
        The new columns are created as NULL, and only become NOT NULL after the migration script copies the data to them.
      • Column Drop
        The column drop occurs after the migration script copies the data to the new columns. At this point, the name column is no longer required and can be safely dropped.

      

   3. Deployment Script - review all changes the deployment will make.
8. Click Deploy Now and confirm the deployment.

SQL Compare deploys the changes to talkr_prod, then performs a recomparison. During deployment, the migration script is executed and copies the data before the original name column is dropped. 

Because the two databases now match, no changes are detected during the recomparison.