How to Identify Duplicate Records in SQL Server and MS CRM | Duplicate Detection Jobs in CRM

HI All,

I would like to share some information regarding Identifying Duplicate Records in SQL Server while Migrating data to CRM and Process to Identify\Remove Duplicate Records from CRM using OOB Configurations. I figure most of you might be aware , but as a revise providing you below info.

1. Identify Number of Duplicate Rows in SQL Server based on selected columns

        Sample Query :

                      

SELECT     ContactId,     [Name],     COUNT(*) occurrences

FROM [Lipscomb_Prod_Migration].[dbo].[Previous_Education_Radius_Produ$]

GROUP BY

               ContactId, [Name]

HAVING     COUNT(*) > 1;

2. Delete Duplicate Rows in Staging SQL Syntax :

Sample Query :

WITH cte AS (

    SELECT

       ContactId,

 [Name] ,       

        ROW_NUMBER() OVER (

            PARTITION BY

                ContactId, [Name]

               

            ORDER BY

               ContactId, [Name]

        ) row_num

     FROM

        [Lipscomb_Prod_Migration].[dbo].[Previous_Education_Radius_Produ$]

)

DELETE FROM cte

WHERE row_num > 1;

3. Duplication Detection of Records in CRM :

• Navigate to Settings > Data Management > Duplicate Detection Jobs
• Create a New Duplicate Detection Job and select Entity which you want to find duplicate records
• Run the Detection Job so that below pop-up window will display :

Where we can take necessary action on the record.