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 Temparory Table
Create TAble #YourTable
( YourTempId int, YourTempName Char(30) )
--Inserting Rows
insert into #YourTable (YourTempId,YourTempName) values (1,'Abhishek')
insert into #YourTable (YourTempId,YourTempName) values (2,'Abhishek')
insert into #YourTable (YourTempId,YourTempName) values (1,'Abhishek')
insert into #YourTable (YourTempId,YourTempName) values (1,'Abhishek')
insert into #YourTable (YourTempId,YourTempName) values (3,'Abhishek')
insert into #YourTable (YourTempId,YourTempName) values (2,'Abhishek')
--Now check the table
SELECT * FROM #YourTable
--Create Duplicate Table
Create TAble #DuplicateTable
( DuplicateTempId int, DuplicateTempName Char(30) )
--Getting Duplicate Records from Original table
insert into #DuplicateTable (DuplicateTempId,DuplicateTempName) (
SELECT YourTempId,YourTempName FROM #YourTable GROUP BY YourTempId,YourTempName HAVING COUNT(YourTempId)>1)
--Again check Duplicate table
SELECT * FROM #DuplicateTable
--DELETE RECORDS FROM original TABLE
DELETE FROM #YourTable
FROM #YourTable
INNER JOIN #DuplicateTable Duplicate ON Duplicate.DuplicateTempId = #YourTable.YourTempId
--Again check your original table
SELECT * FROM #YourTable
--INSERT Records in Original table from Temparory table
INSERT INTO #YourTable (YourTempId,YourTempName)
(SELECT DuplicateTempId,DuplicateTempName FROM #DuplicateTable)
--Now you can check that the Original table contains only unique records.
SELECT * FROM #YourTable