Find Duplicate records from a Database Table and Delete them

To Find Out Duplicate records in a Table and Delete them:-

For this operation i am creating a temparory table and insert records in it, with some duplicate rows. First i will find the duplicate rows and insert it in a temparory table, then delete all records from Original table which are related to Duplicate rows.

Then Insert rows from Temparory table to the original table. Now you can find the unique records in your original table.

--Creating Table

Create TAble emp( id int, name Char(30) )

--Inserting Rows

insert into emp (id,name) values (1,'adi')

insert into emp (id,name) values (2,'jc.adi')

insert into emp (id,name) values (1,'adi')

insert into emp (id,name) values (1,'adi')

insert into emp (id,name) values (3,'adi')

insert into emp (id,name) values (2,'adi')

--Now check the table

SELECT * FROM emp

--Create Duplicate Table

Create TAble #DuplicateTable

( DuplicateId int, DuplicateName Char(30) )

--Getting Duplicate Records from Original table

insert into #DuplicateTable (DuplicateId,DuplicateName) (

SELECT id,name FROM emp GROUP BY id,name HAVING COUNT(id)>1)

--Again check Duplicate table

SELECT * FROM #DuplicateTable

--DELETE RECORDS FROM original TABLE

DELETE FROM emp

FROM emp

INNER JOIN #DuplicateTable Duplicate ON Duplicate.DuplicateId = emp.id

--Again check your original table

SELECT * FROM emp

--INSERT Records in Original table from Temparory table

INSERT INTO emp (id,name)

(SELECT DuplicateId,DuplicateName FROM #DuplicateTable)

--Now you can check that the Original table contains only unique records.

SELECT * FROM emp

(OR)

SET ROWCOUNT 1

DELETE emp

FROM emp a

WHERE (SELECT COUNT(*) FROM emp b WHERE b.id =

a.id AND b.name = a.name ) > 1

WHILE @@rowcount > 0

  DELETE emp

  FROM emp a

  WHERE (SELECT COUNT(*) FROM emp b WHERE b.id =

a.id AND b.name = a.name) > 1

SET ROWCOUNT 0