Sql-server – Migrate data from Child to Parent table – Flatten based on logic

performancequery-performancesql serversql-server-2016

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

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.

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');