WholeScript.sql

-- Check data
SELECT TOP (100)
    [CustomerKey], [EmailAddress]
FROM [ContosoRetailDW_DEV].[dbo].[DimCustomer]

[EmailAddress]:
[EmailPrefix]
[EmailDomain]


SELECT TOP (5)
    [CustomerKey], [EmailAddress]
FROM [ContosoRetailDW_DEV].[dbo].[DimCustomer]
WHERE [EmailAddress] IS NULL




-- Plan:
-- 1. Copy existing data (only involved columns + PK) to temp table
-- 2. Change the structure (remove column, add the new ones)
-- 3. Update new column(s) with data from temp table






-- Step 1: Pre-Deployment
SELECT [CustomerKey], [EmailAddress]
INTO #DimCustomer
FROM [dbo].[DimCustomer]

ALTER TABLE #DimCustomer ADD CONSTRAINT [PK_#DimCustomer] PRIMARY KEY CLUSTERED 
(	[CustomerKey] ASC )
GO






-- Manual check
SELECT * FROM #DimCustomer







-- Step 2: this will be done by migration script generated by SSDT/SQLPackage







-- Step 3: Post-deployment
;WITH tmp AS (
	SELECT [CustomerKey], EmailAddress
	--, CHARINDEX('@', EmailAddress)
	, SUBSTRING(EmailAddress, 1, CHARINDEX('@', EmailAddress)-1) as EmailPrefix
	, SUBSTRING(EmailAddress, CHARINDEX('@', EmailAddress)+1, 99) as EmailDomain
	FROM #DimCustomer
)
UPDATE C
set [EmailPrefix] = tmp.EmailPrefix
  ,	[EmailDomain] = tmp.EmailDomain
FROM [dbo].[DimCustomer] as C
JOIN tmp ON tmp.[CustomerKey] = C.[CustomerKey]





-- Check result
SELECT TOP (100)
     *
FROM [ContosoRetailDW_DEV].[dbo].[DimCustomer]

SELECT TOP (5)
    [CustomerKey], [EmailPrefix], [EmailDomain]
FROM [ContosoRetailDW_DEV].[dbo].[DimCustomer]
WHERE [EmailPrefix] IS NULL


SELECT TOP (100) *
FROM [ContosoRetailDW_DEV].[dbo].[DimCustomer]



--Secure


-- MS template:
IF OBJECT_ID(N'dbo.__RefactorLog') IS NULL
BEGIN
    CREATE TABLE [dbo].[__RefactorLog] (OperationKey UNIQUEIDENTIFIER NOT NULL PRIMARY KEY)
    EXEC sp_addextendedproperty N'microsoft_database_tools_support', N'refactoring log', N'schema', N'dbo', N'table', N'__RefactorLog'
END
GO
IF NOT EXISTS (SELECT OperationKey FROM [dbo].[__RefactorLog] WHERE OperationKey = 'f239986f-376b-4ada-a529-00b94b59330d')
INSERT INTO [dbo].[__RefactorLog] (OperationKey) values ('f239986f-376b-4ada-a529-00b94b59330d')




-- Let's reuse the code:
select NEWID()


IF OBJECT_ID(N'dbo.__RefactorLog') IS NULL
BEGIN
    CREATE TABLE [dbo].[__RefactorLog] (OperationKey UNIQUEIDENTIFIER NOT NULL PRIMARY KEY)
    EXEC sp_addextendedproperty N'microsoft_database_tools_support', N'refactoring log', N'schema', N'dbo', N'table', N'__RefactorLog'
END
GO
IF NOT EXISTS (SELECT OperationKey FROM [dbo].[__RefactorLog] WHERE OperationKey = 'AE3B26BF-C2C9-42E3-8400-7B0451C694BB')
INSERT INTO [dbo].[__RefactorLog] (OperationKey) values ('AE3B26BF-C2C9-42E3-8400-7B0451C694BB')

select * from [dbo].[__RefactorLog]



IF NOT EXISTS (SELECT OperationKey FROM [dbo].[__RefactorLog] WHERE OperationKey = 'AE3B26BF-C2C9-42E3-8400-7B0451C694BB')
BEGIN

	SELECT [CustomerKey], [EmailAddress]
	INTO #DimCustomer
	FROM [dbo].[DimCustomer]

	ALTER TABLE #DimCustomer ADD CONSTRAINT [PK_#DimCustomer] PRIMARY KEY CLUSTERED 
	(	[CustomerKey] ASC )

END

GO

--------------------------------

IF NOT EXISTS (SELECT OperationKey FROM [dbo].[__RefactorLog] WHERE OperationKey = 'AE3B26BF-C2C9-42E3-8400-7B0451C694BB')
BEGIN

	EXEC ('SELECT [CustomerKey], [EmailAddress] INTO #DimCustomer FROM [dbo].[DimCustomer]')

	ALTER TABLE #DimCustomer ADD CONSTRAINT [PK_#DimCustomer] PRIMARY KEY CLUSTERED 
	(	[CustomerKey] ASC )

END

GO
















-- HELP: Copy data (from original db)
USE [ContosoRetailDW_DEV]
GO
DELETE FROM [ContosoRetailDW_DEV].[dbo].[DimCustomer]

SET IDENTITY_INSERT [dbo].[DimCustomer] on

INSERT INTO [ContosoRetailDW_DEV].[dbo].[DimCustomer]
( [CustomerKey], [GeographyKey], [CustomerLabel], [Title], [FirstName], [MiddleName], [LastName], [NameStyle], [BirthDate], [MaritalStatus], [Suffix], [Gender], [EmailAddress], [YearlyIncome], [TotalChildren], [NumberChildrenAtHome], [Education], [Occupation], [HouseOwnerFlag], [NumberCarsOwned], [AddressLine1], [AddressLine2], [Phone], [DateFirstPurchase], [CustomerType], [CompanyName], [ETLLoadID], [LoadDate], [UpdateDate] )
SELECT 
  [CustomerKey], [GeographyKey], [CustomerLabel], [Title], [FirstName], [MiddleName], [LastName], [NameStyle], [BirthDate], [MaritalStatus], [Suffix], [Gender], [EmailAddress], [YearlyIncome], [TotalChildren], [NumberChildrenAtHome], [Education], [Occupation], [HouseOwnerFlag], [NumberCarsOwned], [AddressLine1], [AddressLine2], [Phone], [DateFirstPurchase], [CustomerType], [CompanyName], [ETLLoadID], [LoadDate], [UpdateDate]
FROM [ContosoRetailDW].[dbo].[DimCustomer]

SET IDENTITY_INSERT [dbo].[DimCustomer] OFF

select * from [dbo].[DimCustomer]