SQL Server: How to find duplicates in a table?
Posted by cavemansblog on June 12, 2009
Following is a example of how a table can be queried to identify duplicates based on one more more columns:
--Create a temporary table variable
declare @employee Table(empId int identity(1,1), empName Varchar(50), Salary int)
-- Insert dummy data(empName, Salary)
Insert into @employee Values('test1', 1000)
Insert into @employee Values('test2', 2000)
Insert into @employee Values('test3', 3000)
Insert into @employee Values('test4', 4000)
Insert into @employee Values('test1', 1000)
Insert into @employee Values('test1', 1000)
Insert into @employee Values('test3', 3000)
--Query to identity all the duplicate employee records
select empName, Count(*) NumOfDuplicates from @employee
group by empName
having count(*) > 1
This query will return all the employee names who have duplicates instances/rows in the employee table and also displays the number of times they were duplicated. The query returned two rows with 3 instances of ‘test1′ and 2 instances of ‘test3′ in our employee table as seen in the picture below. Note that I have used a temporary table variable in this example as opposed to a temp table.
Another way of identifying the duplicates is by using a self join as follows:
select * from @employee a where 1 < (select count(*) from @employee b where a.empname = b.empname)
and the following diagram shows the results
