SQL Server Data Migration – Flattening Data from Child to Parent Table

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