Merge Duplicates in Database

Description

User can be in a situation when he or she wants to turn on duplicity check, but he or she already has duplicate companies in database. Each version of company has different relations so it will be difficult to transfer them manually.

Version

All versions of eWay-CRM

Difficulty

High

Resolution

First, you need to find out the duplicates. The following script displays company unique identification, ID, name and RowNumber. RowNumber allows you to decide, what company should be preserved. If the RowNumber is 1, it is the oldest entry of company.

SELECT ItemGUID, IdentificationNumber, FileAs, ROW_NUMBER() OVER (PARTITION BY IdentificationNumber ORDER BY ItemCreated) RowNumber
FROM EWD_Companies
WHERE IdentificationNumber IN
(
    SELECT IdentificationNumber
    FROM EWD_Companies
    WHERE IdentificationNumber IS NOT NULL AND LEN(IdentificationNumber) > 1
    GROUP BY IdentificationNumber
    HAVING COUNT(*) > 1
)

Now, you can merge duplicates using eWaySP_MergeItems procedure. You just specify the item you copy from and item you copy to.

EXEC eWaySP_MergeItems
    @FolderName = 'Companies',
    @TargetItemGUID = 'BDB68D03-96CE-4ED1-83B3-245813D1CBC9', -- GUID of target company
    @SourceItemGUID1 = 'BDB68D03-96CE-4ED1-83B3-245813D1CBC9', -- again, GUID of target company, because we do not want to rewrite attributes of the company, we only want to transfer relations
    @SourceItemGUID2 = '684FDB63-2A26-4C56-B82A-AA1A8D2D09F2' -- GUID of duplicate entry

Then, you need to erase duplicate entry:

EXEC eWaySP_DeleteItem @ItemGUID = '684FDB63-2A26-4C56-B82A-AA1A8D2D09F2', @FolderName = 'Companies'

Now, eWay-CRM contains only one entry about the company, but with preserved relations from erased duplicate.