SQL SERVER - How To Delete Duplicate Records From a Table

Here is the process to delete the duplicate records from a table

Let us first create a table called Product.

Create Table Product (ProdId int, ProdDesc varchar(25))

Insert Into Product
Select 1,'Adjustable Race'
Union All
Select 2,'Bearing Ball'
Union All
Select 1,'Adjustable Race'
Union All
Select 3,'Chainring Bolts'
Union All
Select 4,'Down Tube'
Union All
Select 4,'Road End Caps'
Union All
Select 1,'Adjustable Race'
Union All
Select 6,'Chainring'
Union All
Select 1,'Adjustable Race'
Union All
Select 7,'Headset Ball Bearings'
Union All
Select 8,'Blade'
Union All
Select 3,'Chainring Bolts'
Union All
Select 9,'Chainring Nut'

Now the records in the table are as shown below :

               

Now, In Order to delete the Duplicate records from the Product table, As a first step, we will check the number of sets of duplicate Primary Key values that exist and the count of duplicates for each set.

Select the duplicate key values in to a original table Duplicate_Products.

SELECT ProdId, ProdDesc,Count(*) RecCount INTO Duplicate_Products FROM Product GROUP BY ProdId, ProdDesc
HAVING Count(*) > 1

On retrieving the records from  Duplicate_Products,     

Select * From Duplicate_Products

      

Now, Select the duplicate rows into a original table, eliminating duplicates in the process.

SELECT DISTINCT P1.* INTO Unique_Products FROM Product P1, Duplicate_Products P2 WHERE P1.ProdId = P2.ProdId AND P1.ProdDesc = P2.ProdDesc

On retrieving the records from  Unique_Products,

Select * from Unique_Products

       

At this point, the Unique_Products table should have unique Primary Keys.

Now the next step is, Deleting the duplicate rows from the original table Product.

DELETE  Product FROM Product P1,Duplicate_Products P2  WHERE P1.ProdId =  P2.ProdId AND P1.ProdDesc = P2.ProdDesc

Now Put the unique rows back in the original table Product.

INSERT Product SELECT * FROM Unique_Products

Now On retrieving the records from Product table,we can see that all the duplicate records has been deleted.

SELECT * From Product