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.

SqlDuplicates

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

Advertisements

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!!

    test3

    September 26, 2012 at 2:07 am


Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

%d bloggers like this: