Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
DB Migration Test Example
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');
-- 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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment