Caveman's Blog

My commitment to learning.

SQL Server: How to find duplicates in a table?

with one comment

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) [/sourcecode] and the following diagram shows the results SqlDuplicates2


Written by cavemansblog

June 12, 2009 at 12:49 pm

One Response

Subscribe to comments with RSS.

  1. Hi, i believe that i saw you visited my weblog so i came to return the choose?.I’m trying to to find issues to enhance my web site!I assume its good enough to make use of a few of your ideas!!


    September 26, 2012 at 2:07 am

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: