0

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 a TYPE 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
  • Modified the query to add update stmt for child table in all the IF blocks, everytime that parent record is updated with email_address, all the remaining records in the child table must be updated with track = 1 except for the one that got updated in the parent table. – miledba Mar 3 '17 at 20:13
1

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.

UPDATE mc
SET EMAIL_ADDRESS = uci.DETAIL
FROM dbo.[MyContacts] mc
CROSS APPLY (SELECT TOP 1 ci.DETAIL 
                    FROM dbo.[CustomerInfo] ci 
                    WHERE ci.ID = mc.ID 
                    AND ci.CONTACT_ID = mc.CONTACT_ID 
                    AND ci.TYPE = 'EMAIL'
                    AND ci.LABEL IN ('WORK', 'BUSINESS', 'PERSONAL')

                ORDER BY CASE ci.LABEL 
                                WHEN 'WORK' THEN 1 
                                WHEN 'BUSINESS' THEN 2 
                                WHEN 'PERSONAL' THEN 3 
                                ELSE 4 
                                END ASC
            ) uci;

UPDATE dbo.[CustomerInfo]
SET TRACK = 1 
WHERE TYPE = 'EMAIL'
AND LABEL NOT IN ('WORK', 'BUSINESS', 'PERSONAL');
  • Sorry, I had to add another update in there. As soon as parent table is updated with top 1 for any of the labels, remaining records must be updated in the child table - CustomerInfo and track flag must be set to 1. Is there a way we can squeeze that logic into your code. Many thanks!! – miledba Mar 3 '17 at 20:08
  • Hi, "remaining records must be updated in the child table" - updated to what? Just the email records, or all? And does soon as mean immediately? Not sure that'll work with this method. – Gareth Lyons Mar 7 '17 at 13:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.