Friday, May 31, 2013

Find Duplicate Record in Sql Server


Introduction

In this article i will explain how to find or count duplicate record in SQL Server.

In this example i have a datatable and this table doesn't contain any primary key because of that duplicate records inserted in table.

First i will create table without contain any primary key

Query


CREATE TABLE [dbo].[Emp_Info]
(
      [Emp_Id] [int] NULL,
      [Name] [varchar](50) NULL,
      [Salary] [int] NULL,
      [City] [varchar](50) NULL

)
Now insert some duplicate record in this table.




Now count how many duplicate record exits in datatable with the help of below query

Query


select Emp_Id, Name, Count(*) as DuplicateRecord
From Emp_Info
Group By Emp_Id, Name, City

Having Count(*) >1 Order By Emp_Id