DB Migration Test Example

01_setup.sql

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Users](
	[id] [INT] IDENTITY(1,1) NOT NULL,
	[client_product_code] [NVARCHAR](50) NULL,
 CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

INSERT INTO [dbo].[Users] (client_product_code) VALUES ('123_abc');

02_migration_test.sql

-- Remember to install the tSQLt framework, and create a test class to hold your migration tests

EXEC tSQLt.NewTestClass 'migration_tests';
GO


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [migration_tests].[test split_column]
AS
BEGIN
  --Assemble
	EXEC tSQLt.FakeTable 'dbo.Users';
	INSERT INTO dbo.Users (id, client_product_code) VALUES (1, '123_456'), (2, '789_012')
  
  --Act
  	-- Schema Migration
	EXECUTE sp_executeSQL N'
		ALTER TABLE dbo.Users ADD 
		client_code NVARCHAR(50) NULL, 
		product_code NVARCHAR(50) NULL ;
	'
	-- Data Migration
	EXECUTE sp_executeSQL N'
		UPDATE dbo.Users  
			SET client_code=SUBSTRING(client_product_code, 0, CHARINDEX(''_'', client_product_code));
		UPDATE dbo.Users  
			SET product_code=SUBSTRING(client_product_code,CHARINDEX(''_'', client_product_code)+1, LEN(client_product_code) );
	'
  
  --Assert
  	EXECUTE sp_executeSQL N'
		DECLARE @client_code NVARCHAR(50);
		DECLARE @product_code NVARCHAR(50);
		SELECT @client_code = client_code FROM dbo.Users WHERE id = 1;
		SELECT @product_code =  product_code FROM dbo.Users WHERE id = 1;
		EXEC tSQLt.assertEquals ''123'', @client_code;
		EXEC tSQLt.assertEquals ''456'', @product_code ;

		SELECT @client_code = client_code FROM dbo.Users WHERE id = 2;
		SELECT @product_code =  product_code FROM dbo.Users WHERE id = 2;
		EXEC tSQLt.assertEquals ''789'', @client_code;
		EXEC tSQLt.assertEquals ''012'', @product_code ;
	'
  
END;


SET ANSI_NULLS ON

GO