Remove Duplicate Records from SQL table
This Code will help to remove duplicate records when there is not key field in the table. Either we have string data or the fields in the table can not be used to uniquely identify the data. With the help of this code one can easily delete the duplicate data without using any loop or trigger.
To Delete the Duplicate record when there is no unique key defined, we can achieve this with the help of "WITH" CTE(Common Table Expression). with the help of this CTE we can create a temporary result set that, the scope of the CTE is within the single execution. that mean you can not run the with statement alone. you need to execute the following code in one go.
/* Mark Duplicate Record */
WITH TempEmp (Name,duplicateRecCount)
AS
(
SELECT Name,ROW_NUMBER() OVER(PARTITION by Name, Salary, Designation ORDER BY Name)
AS duplicateRecCount
FROM #Employee
)
/* Now Delete Duplicate Records */
DELETE FROM TempEmp
WHERE duplicateRecCount > 1
[B]Complete Code Snippet:
=========================[/B]
/* Create Table Statement */
CREATE TABLE #Employee
(
Name varchar(55) NULL,
Salary decimal(10, 2) NULL,
Designation varchar(20) NULL
)
/* Insert Data into the Table*/
INSERT INTO #Employee VALUES('Lakhan Pal Garg', 10000, 'AST')
INSERT INTO #Employee VALUES('Lakhan Pal Garg', 10000, 'AST')
INSERT INTO #Employee VALUES('Amit Tyagi', 10000, 'ITA')
INSERT INTO #Employee VALUES('Amit Tyagi', 10000, 'ITA')
INSERT INTO #Employee VALUES('Lakhan Pal Garg', 12000, 'AST')
INSERT INTO #Employee VALUES('Amit Tyagi', 13000, 'ITA')
INSERT INTO #Employee VALUES('Sumit Sharma', 15000, 'AST')
INSERT INTO #Employee VALUES('Sumit Sharma', 15500, 'AST')
/* Mark Duplicate Record */
WITH TempEmp (Name,duplicateRecCount)
AS
(
SELECT Name,ROW_NUMBER() OVER(PARTITION by Name, Salary, Designation ORDER BY Name)
AS duplicateRecCount
FROM #Employee
)
/* Now Delete Duplicate Records */
DELETE FROM TempEmp
WHERE duplicateRecCount > 1
/* See updated table data */
Select * from #Employee
hi,
we can also delete duplicate row from sql server as :-
TEST table has duplicate row, then
--create Temporary Table TEMP : -
SELECT *INTO TEMP FROM (SELECT DISTINCT * FROM TEST)T
--Drop Original Table TEST
DROP TABLE TEST
-- Then again create Original Table
SELECT *INTO TEST FROM TEMP
-- Drop Temporary Table TEMP
DROP TABLE TEMP
Dharmendra