How To Remove Duplicate Records From A Table

 

Consider Following Table For Example Which Contains Duplicate Country Names :

Select * From Country_Master

Go

 

Let’s See How Many Duplicate Records Each Country Contains.

Select CountryName,COUNT(CountryName) as NoOfRecords from Country_Master
Group By CountryName
Go

 

Now You Can Use Following Query to Remove Duplicate Rows

Declare @CountryName Varchar(100)
Declare @Records Int
Declare DelDuplicate Cursor 

For
Select CountryName,COUNT(CountryName)-1 as NoOfRecords  --Get Count Of (Records Country Wise)-1
from Country_Master
Group By CountryName
Having Count(CountryName)>1 -- Include Only Those Records which are More then 1

Open DelDuplicate
Fetch DelDuplicate Into @CountryName,@Records
While @@FETCH_STATUS=0
    Begin
        Set RowCount @Records -- Set RowCount (Total Record Country Wise) - 1
            Delete From Country_Master Where CountryName=@CountryName -- Delete Duplicate Records
        Set RowCount 0 -- Reset Record To Default
        Fetch DelDuplicate Into @CountryName,@Records
    End 
Close DelDuplicate 
DeAllocate DelDuplicate

 

As You Can See the Major Role Here Is Played By RowCount

For Example When I Set RowCount = 1 and you run Select Command On table Then Irrespective of No Of Records In Table. Result will Show only 1 Row and Even  if You Delete From Table only 1 row Will Be Deleted.

Default Value Of RowCount is 0.

Which Returns All The Rows Of Table in Select Statement.

 

Now Let’s See What happened to the Table containing Duplicate Country Names