Thursday, November 23, 2017

delete duplicate records from table and reference tables




declare @id varchar(MAX)


Create Table #MyDetails(cid BigInt,uqId varchar(max))

--select count(uniqueid) as t,uniqueID from companydetails group by uniqueid having count(uniqueid)>1 order by t desc

INSERT into #MyDetails(cid,uqId) (select count(uniqueid) as t,uniqueID from companydetails group by uniqueid having count(uniqueid)>1)




 DECLARE id_Cursor CURSOR FOR select uqId from #MyDetails
OPEN id_Cursor;
FETCH NEXT FROM id_Cursor into @id;
WHILE @@FETCH_STATUS = 0
   BEGIN
  --
    Exec [dbo].[sp_deleteDuplicateCompanyIds] @id
FETCH NEXT FROM id_Cursor into @id;
    END;
CLOSE id_Cursor;
DEALLOCATE id_Cursor;




create PROCEDURE [dbo].[sp_deleteDuplicateCompanyIds]
@uID   Nvarchar(Max)
AS
BEGIN

Declare @MaxId BigInt

--Set @uID='0000000003983694'
select @MaxId =  max(CompanyDetailsID) from [dbo].[CompanyDetails] where UniqueId=@uID

update [dbo].[BeneficialShareHolder] set companydetailsid=@MaxId where CompanyDetailsID in
(select [CompanyDetailsID] from [dbo].[CompanyDetails] where UniqueId=@uID and CompanyDetailsID <> (@MaxId))

--2.2

update [dbo].FundHolderDetails set companydetailsid=@MaxId where CompanyDetailsID in
(select [CompanyDetailsID] from [dbo].[CompanyDetails] where UniqueId=@uID and CompanyDetailsID <> (@MaxId))

--3
delete from [CompanyDetails]  where CompanyDetailsID in
(select [CompanyDetailsID] from [dbo].[CompanyDetails] where UniqueId=@uID and CompanyDetailsID <> (@MaxId))


/// <summary>
/// Remove duplicate records
/// </summary>
/// <param name="table">DataTable for removing duplicate records</param>
/// <param name="DistinctColumn">Column to check for duplicate values or records</param>
/// <returns></returns>
public DataTable RemoveDuplicateRows(DataTable table, string DistinctColumn)
{
    try
    {
        ArrayList UniqueRecords = new ArrayList();
        ArrayList DuplicateRecords = new ArrayList();

        // Check if records is already added to UniqueRecords otherwise,
        // Add the records to DuplicateRecords
        foreach(DataRow dRow in table.Rows)
        {
            if (UniqueRecords.Contains(dRow[DistinctColumn]))
                DuplicateRecords.Add(dRow);
            else
                UniqueRecords.Add(dRow[DistinctColumn]);
        }

        // Remove duplicate rows from DataTable added to DuplicateRecords
        foreach (DataRow dRow in DuplicateRecords)
        {
            table.Rows.Remove(dRow);
        }

        // Return the clean DataTable which contains unique records.
        return table;
    }
    catch (Exception ex)
    {
        return null;
    }
}

DataTable DuplicateRec = objDatabase.getTable("SQL Query");
DataTable UniqueRec = RemoveDuplicateRows
(DuplicateRec, "Column Name to check for duplicate records");