Delete Duplicate values
IF OBJECT_ID('tempdb..#tmpDuplicateVals') IS NOT NULL
DROP TABLE #tmpDuplicateVals
CREATE TABLE #tmpDuplicateVals( Col1 VARCHAR(3),
Col2 VARCHAR(3),
Col3 VARCHAR(3) )
insert into #tmpDuplicateVals values('aa1','aa1','aa1')
insert into #tmpDuplicateVals values('aa1','aa1','aa1')
insert into #tmpDuplicateVals values('aa2','aa2','aa2')
insert into #tmpDuplicateVals values('aa2','aa2','aa2')
insert into #tmpDuplicateVals values('aa3','aa3','aa3')
insert into #tmpDuplicateVals values('aa3','aa3','aa3')
insert into #tmpDuplicateVals values('aa4','aa4','aa4')
insert into #tmpDuplicateVals values('aa4','aa4','aa4')
insert into #tmpDuplicateVals values('aa5','aa5','aa5')
insert into #tmpDuplicateVals values('aa5','aa5','aa5')
SELECT * FROM #tmpDuplicateVals
BEGIN
WITH cteDV( RID, Col1, Col2, Col3 ) AS
( SELECT ROW_NUMBER() OVER ( PARTITION BY Col1, Col2, Col3 ORDER BY Col1, Col2, Col3 ) RID, *
FROM #tmpDuplicateVals
)
DELETE FROM cteDV WHERE RID = 1
END
SELECT * FROM #tmpDuplicateVals
IF OBJECT_ID('tempdb..#tmpDuplicateVals') IS NOT NULL
DROP TABLE #tmpDuplicateVals