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