I need to migrate emails from [CustomerInfo]
table to its parent table [MyContacts]
. For each ID
and Contact_ID
from MyContacts
tables we need to loop through the code below:
- In
[CustomerInfo]
,EMAIL
is aTYPE
and can have different labels like work, personal, business etc.DETAIL
column has the actual email address. - When migrating the data, I need to prioritize based on label order (work, business, personal) i.e. if the label is WORK, then get the top WORK email (since there could be many work emails and we need to migrate only one record to parent table) and update
[MyContacts].[EMAIL_ADDRESS]
with[CustomerInfo].[DETAIL]
(DETAIL
column has the actual email address). - Else if there is no WORK email then check if
LABEL
= 'BUSINESS' and update the[MyContacts].[EMAIL_ADDRESS]
and so on. If none of them exists then update track flag in the child table –CustomerInfo
.
The problem is that I have to loop through millions of records to do this and flatten the table and it is taking forever. Is there a better way to write this script efficiently using case
stmt or anything else?
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MyContacts] (
[ID] [int] NOT NULL
,[CONTACT_ID] [bigint] IDENTITY(1, 1) NOT NULL
,[EMAIL_ADDRESS] [varchar](150) NULL
,CONSTRAINT [PK_CONTACT] PRIMARY KEY CLUSTERED (
[ID] ASC
,[CONTACT_ID] ASC
) WITH (
PAD_INDEX = OFF
,STATISTICS_NORECOMPUTE = OFF
,IGNORE_DUP_KEY = OFF
,ALLOW_ROW_LOCKS = ON
,ALLOW_PAGE_LOCKS = ON
,FILLFACTOR = 90
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[CustomerInfo] (
[ID] [int] NOT NULL
,[CONTACT_ID] [bigint] NOT NULL
,[CustomerInfo_CONTACT_ID] [bigint] IDENTITY(1, 1) NOT NULL
,[TYPE] [varchar](50) NOT NULL
,[LABEL] [nvarchar](50) NOT NULL
,[DETAIL] [varchar](150) NULL
,[TRACK] INT NULL
,CONSTRAINT [PK_CustomerInfo] PRIMARY KEY CLUSTERED (
[ID] ASC
,[CONTACT_ID] ASC
,[CustomerInfo_CONTACT_ID] ASC
) WITH (
PAD_INDEX = OFF
,STATISTICS_NORECOMPUTE = OFF
,IGNORE_DUP_KEY = OFF
,ALLOW_ROW_LOCKS = ON
,ALLOW_PAGE_LOCKS = ON
,FILLFACTOR = 90
) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING ON
ALTER TABLE [dbo].[CustomerInfo]
WITH CHECK ADD CONSTRAINT [FK_CustomerInfo] FOREIGN KEY (
[ID]
,[CONTACT_ID]
) REFERENCES [dbo].[MyContacts]([ID], [CONTACT_ID])
GO
ALTER TABLE [dbo].[CustomerInfo] CHECK CONSTRAINT [FK_CustomerInfo]
GO
DECLARE @id INT = 100
,@contactId BIGINT = 2121
IF EXISTS (
SELECT CustomerInfo_CONTACT_ID
FROM dbo.[CustomerInfo] WITH (NOLOCK)
WHERE ID = @id
AND CONTACT_ID = @contactId
AND TYPE = 'EMAIL'
)
BEGIN
DECLARE @emailToMigrate VARCHAR(150)
IF EXISTS (
SELECT CustomerInfo_CONTACT_ID
FROM dbo.[CustomerInfo] WITH (NOLOCK)
WHERE ID = @id
AND CONTACT_ID = @contactId
AND TYPE = 'EMAIL'
AND LABEL = 'WORK'
)
BEGIN
SET @emailToMigrate = (
SELECT TOP 1 DETAIL
FROM dbo.[CustomerInfo] WITH (NOLOCK)
WHERE ID = @id
AND CONTACT_ID = @contactId
AND TYPE = 'EMAIL'
AND LABEL = 'WORK'
);
UPDATE dbo.[MyContacts]
SET EMAIL_ADDRESS = @emailToMigrate
WHERE ID = @id
AND CONTACT_ID = @contactId;
UPDATE dbo.[CustomerInfo]
SET TRACK = 1
WHERE ID = @id
AND CONTACT_ID = @contactId
AND TYPE = 'EMAIL'
AND DETAIL != @emailToMigrate
END
ELSE IF EXISTS (
SELECT CustomerInfo_CONTACT_ID
FROM dbo.[CustomerInfo] WITH (NOLOCK)
WHERE ID = @id
AND CONTACT_ID = @contactId
AND TYPE = 'EMAIL'
AND LABEL = 'BUSINESS'
)
BEGIN
SET @emailToMigrate = (
SELECT TOP 1 DETAIL
FROM dbo.[CustomerInfo] WITH (NOLOCK)
WHERE ID = @id
AND CONTACT_ID = @contactId
AND TYPE = 'EMAIL'
AND LABEL = 'BUSINESS'
);
UPDATE dbo.[MyContacts]
SET EMAIL_ADDRESS = @emailToMigrate
WHERE ID = @id
AND CONTACT_ID = @contactId;
UPDATE dbo.[CustomerInfo]
SET TRACK = 1
WHERE ID = @id
AND CONTACT_ID = @contactId
AND TYPE = 'EMAIL'
AND DETAIL != @emailToMigrate
END
ELSE IF EXISTS (
SELECT CustomerInfo_CONTACT_ID
FROM dbo.[CustomerInfo] WITH (NOLOCK)
WHERE ID = @id
AND CONTACT_ID = @contactId
AND TYPE = 'EMAIL'
AND LABEL = 'PERSONAL'
)
BEGIN
SET @emailToMigrate = (
SELECT TOP 1 DETAIL
FROM dbo.[CustomerInfo]
WHERE ID = @id
AND CONTACT_ID = @contactId
AND TYPE = 'EMAIL'
AND LABEL = 'PERSONAL'
);
UPDATE dbo.[MyContacts]
SET EMAIL_ADDRESS = @emailToMigrate
WHERE ID = @id
AND CONTACT_ID = @contactId;
UPDATE dbo.[CustomerInfo]
SET TRACK = 1
WHERE ID = @id
AND CONTACT_ID = @contactId
AND TYPE = 'EMAIL'
AND DETAIL != @emailToMigrate
END
ELSE
BEGIN
UPDATE dbo.[CustomerInfo]
SET TRACK = 1
WHERE ID = @id
AND CONTACT_ID = @contactId
AND TYPE = 'EMAIL';
END
END
Best Answer
Here's a set-based solution you could try. Think I've got your requirements right. As it's millions of records you're updating, it might need to be broken into chunks depending on your available downtime window.