SQL Source Control 3

Help for older versions available.

These pages cover SQL Source Control 3, which is not the latest version. Help for other versions is also available.

Example: writing a V2 migration script that affects objects not yet in the target database

Page last updated 04 December 2015

We're going to remove the migrations V2 beta from SQL Source Control in version 5. We're replacing it with an improved version of the original migrations feature that supports more things (including Git, branching, and merging).

We'll be publishing more information about this soon.

This page refers to the Migrations V2 beta. For information about how to use V1 migration scripts, see Working with migration scripts.

Thanks to user Steve Ognibene for raising this scenario.

1: Normalizing a table

Say we have a script to create a table named dbo.PersonData:

  1. CREATE TABLE dbo.PersonData(
  2.   ID INT IDENTITY(1,1),
  3.   NAME NVARCHAR(200) NOT NULL,
  4.   Email1 NVARCHAR(200) NULL,
  5.   Email2 NVARCHAR(200) NULL,
  6.   Phone1 NVARCHAR(100) NULL,
  7.   Phone2 NVARCHAR(100) NULL,
  8.   Street1 NVARCHAR(200) NULL,
  9.   City1 NVARCHAR(200) NULL,
  10.   StateProvince1 NVARCHAR(50) NULL,
  11.   PostalCode1 NVARCHAR(50) NULL,
  12.   Street2 NVARCHAR(200) NULL,
  13.   City2 NVARCHAR(200) NULL,
  14.   StateProvince2 NVARCHAR(50) NULL,
  15.   PostalCode2 NVARCHAR(50) NULL,
  16.   CONSTRAINT PK_PersonDataID PRIMARY KEY (ID));

We want to normalize the table. To do it, we'll break it into different tables in a new schema named Contacts:

  1. CREATE SCHEMA Contacts
  2. GO
  3. CREATE TABLE Contacts.Person(
  4.     ID INT IDENTITY(1,1),
  5.     NAME NVARCHAR(200),
  6.  	CONSTRAINT PK_PersonID PRIMARY KEY (ID)
  7. 	);
  8.    
  9. CREATE TABLE Contacts.Email(
  10.     PersonID INT,
  11.     Email NVARCHAR(200)
  12. );
  13.  
  14. CREATE TABLE Contacts.Phone(
  15.     PersonID INT,
  16.     Phone NVARCHAR(100)
  17. );
  18.  
  19. CREATE TABLE Contacts.PostAddress(
  20.     PersonID INT,
  21.     Street NVARCHAR(200) NULL,
  22.     City NVARCHAR(200) NULL,
  23.     StateProvince NVARCHAR(50) NULL,
  24.     PostalCode NVARCHAR(50) NULL
  25. );
  26.  
  27. CREATE TABLE Contacts.FavoriteFoods(
  28.   PersonID INT,
  29.   FoodName NVARCHAR(200)
  30. )

Now we'll commit the new schema to source control.

2. Migrating the data

Next we’ll write a migration script to migrate the data from the old dbo.PersonData table to the new tables in the Contacts schema.

To migrate <Id, Name> from dbo.PersonData into [Contacts].Person, we'll write:

  1. --Move data from dbo.PersonData into [Contacts].Person
  2.       SET IDENTITY_INSERT [Contacts].Person ON
  3.       BEGIN
  4.             INSERT INTO [Contacts].Person ( ID, Name)
  5.             SELECT ID, NAME FROM dbo.PersonData
  6.       END
  7.       SET IDENTITY_INSERT [Contacts].Person OFF

To migrate Email1 and Email2 from dbo.PersonData into [Contacts].Person (creating two separate rows for a person having both Email1 and Email2):

  1. --Move data from dbo.PersonData into [Contacts].Email
  2. 	BEGIN
  3.     	INSERT INTO Contacts.Email(PersonID,Email)
  4.     	SELECT ID, Email1 FROM dbo.PersonData WHERE Email1 IS NOT NULL
  5.         UNION
  6.       	SELECT ID, Email2 FROM dbo.PersonData WHERE Email2 IS NOT NULL
  7. 	END

To migrate Phone1 and Phone2 from dbo.PersonData into [Contacts].Phone:

  1. --Move data from dbo.PersonData into [Contacts].Phone
  2.    BEGIN
  3. 		INSERT INTO Contacts.Phone(PersonID,Phone)
  4.       	SELECT ID, Phone1 FROM dbo.PersonData WHERE Phone1 IS NOT NULL
  5.         UNION
  6.    		SELECT ID, Phone2 FROM dbo.PersonData WHERE Phone2 IS NOT NULL
  7.    END

To migrate address details from dbo.PersonData to [Contacts].PostAddress:

  1.  --Move data from dbo.PersonData into [Contacts].PostAddress
  2.       BEGIN
  3.             INSERT INTO Contacts.PostAddress(PersonID,Street,City,StateProvince,PostalCode)
  4.             SELECT ID, Street1,City1,StateProvince1,PostalCode1 FROM dbo.PersonData
  5.             UNION
  6.             SELECT ID, Street2,City2,StateProvince2,PostalCode2 FROM dbo.PersonData
  7.       END

3. Writing guard clauses

Say we want to deploy the changes above to our production database, Prod A. Prod A contains the original dbo.PersonContact table with some data. If we write a migration script without checking that the Contacts schema and the new tables [Contacts].Person, [Contacts].Phone, [Contacts].Email and [Contacts].PostAddress exist in Prod A, the deployment will fail. This is because the database state our migration script is applied to is different from the state we wrote our migration script for. This is particularly easy to miss if you're working on several database changes at the same time, or if you write the migration script some time after creating the schema.

We'll include some guard clauses in the migration script to check the database is in the right state for the script to work. If it isn't, the script will alter your database first so the the rest of the migration script can be applied. This means the migration script will always work, no matter what state the deployment environment is in.

  1. 		IF OBJECT_ID('[dbo].[PersonData]', 'U') IS NOT NULL --check if the denormalized table exists as a first step
  2. 	BEGIN
  3. 		IF NOT EXISTS (SELECT NAME FROM Sys.Schemas WHERE NAME = 'Contacts') --check if Contacts schema exists
  4.     BEGIN...

Now let's add some logic to create the objects if they don't exist when our migration script is applied.

  1. IF NOT EXISTS (SELECT NAME FROM Sys.Schemas WHERE NAME = 'Contacts') --create Contacts schema if it doesn't exist
  2.       BEGIN
  3.       EXEC sp_executesql N'CREATE SCHEMA Contacts'
  4.       --CREATE TABLE #ShouldRunMigration (notUsed bit)
  5.       END
  6.  
  7.       --Create [Contacts].[Person] if it doesn't exist at time of deployment
  8.       IF (OBJECT_ID('[Contacts].[Person]', 'U') IS NULL)
  9.       BEGIN
  10.             CREATE TABLE [Contacts].Person(
  11.             ID INT IDENTITY(1,1),
  12.             NAME NVARCHAR(200),
  13. 			CONSTRAINT PK_PersonID PRIMARY KEY (ID)
  14. 		);
  15.       END...

4. Final migration script

The final migration script looks like this:

  1. IF OBJECT_ID('[dbo].[PersonData]', 'U') IS NOT NULL --check if the denormalized table exists as a first step
  2. BEGIN
  3. IF NOT EXISTS (SELECT NAME FROM Sys.Schemas WHERE NAME = 'Contacts') --create Contacts schema if it doesn't exist
  4.       BEGIN
  5.       EXEC sp_executesql N'CREATE SCHEMA Contacts'
  6.       --CREATE TABLE #ShouldRunMigration (notUsed bit)
  7.       END
  8.  
  9.       --Create [Contacts].[Person] if it doesn't exist at time of deployment
  10.       IF (OBJECT_ID('[Contacts].[Person]', 'U') IS NULL)
  11.       BEGIN
  12.             CREATE TABLE [Contacts].Person(
  13.             ID INT IDENTITY(1,1),
  14.             NAME NVARCHAR(200),
  15. 			CONSTRAINT PK_PersonID PRIMARY KEY (ID)
  16. 		);
  17.       END
  18.  
  19.       --Create [Contacts].[Email] if it doesn't exist
  20.       IF (OBJECT_ID('[Contacts].[Email]', 'U') IS NULL)
  21.       BEGIN
  22.             CREATE TABLE Contacts.Email(
  23.             PersonID INT,
  24.             Email NVARCHAR(200));
  25.       END
  26.  
  27.  
  28.       --Create [Contacts].[Phone] if it doesn't exist
  29.       IF (OBJECT_ID('[Contacts].[Phone]', 'U') IS NULL)
  30.       BEGIN
  31.             CREATE TABLE [Contacts].Phone(
  32.             PersonID INT,
  33.             Phone NVARCHAR(100));
  34.       END
  35.  
  36.       --Create [Contacts].[PostAddress] if it doesn't exist
  37.       IF (OBJECT_ID('[Contacts].[PostAddress]', 'U') IS NULL)
  38.       BEGIN
  39.             CREATE TABLE Contacts.PostAddress(
  40.             PersonID INT,
  41.             Street NVARCHAR(200) NULL,
  42.             City NVARCHAR(200) NULL,
  43.             StateProvince NVARCHAR(50) NULL,
  44.             PostalCode NVARCHAR(50) NULL);
  45.       END
  46.  
  47. /*--Create [Contacts].[FavouriteFoods] doesn't need to be part of the migration script
  48.       IF OBJECT_ID('[Contacts].[FavouriteFoods]', 'U') IS NULL
  49.       BEGIN
  50.       CREATE TABLE Contacts.FavoriteFoods(
  51.         PersonID INT,
  52.         FoodName NVARCHAR(200));
  53.       END
  54.       */
  55.  
  56.       --Move data from dbo.PersonData into [Contacts].Person
  57.       SET IDENTITY_INSERT [Contacts].Person ON
  58.       BEGIN
  59.             INSERT INTO [Contacts].Person(ID, Name)
  60.             SELECT ID, NAME FROM dbo.PersonData
  61.       END
  62.       SET IDENTITY_INSERT [Contacts].Person OFF
  63.  
  64.       --Move data from dbo.PersonData into [Contacts].Email
  65. BEGIN
  66.             INSERT INTO Contacts.Email(PersonID,Email)
  67.             SELECT ID, Email1 FROM dbo.PersonData WHERE Email1 IS NOT NULL
  68.             UNION
  69.             SELECT ID, Email2 FROM dbo.PersonData WHERE Email2 IS NOT NULL
  70.       END
  71.      
  72.       --Move data from dbo.PersonData into [Contacts].Phone
  73.       BEGIN
  74.             INSERT INTO Contacts.Phone(PersonID,Phone)
  75.             SELECT ID, Phone1 FROM dbo.PersonData WHERE Phone1 IS NOT NULL
  76.             UNION
  77.             SELECT ID, Phone2 FROM dbo.PersonData WHERE Phone2 IS NOT NULL
  78.       END
  79.      
  80.       --Move data from dbo.PersonData into [Contacts].PostAddress
  81.       BEGIN
  82.             INSERT INTO Contacts.PostAddress(PersonID,Street,City,StateProvince,PostalCode)
  83.             SELECT ID, Street1,City1,StateProvince1,PostalCode1 FROM dbo.PersonData
  84.             UNION
  85.             SELECT ID, Street2,City2,StateProvince2,PostalCode2 FROM dbo.PersonData
  86.       END
  87.  
  88. END

After running the migration script, we can drop the original dbo.PersonData table as a separate commit.

 

 

Didn't find what you were looking for?

Product Articles

Tips and how-to guides for Redgate products

University

Easy to follow video courses

Community Forums

Ask, discuss, and solve questions about Redgate's tools

Events & Friends

Meet us at an event, get sponsored, and join our Friends of Redgate

Simple Talk

In-depth articles and opinion from Redgate's technical journal

Cookies on red-gate.com

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy