# Create table with duplicate records
CREATE TABLE tblTest (Col1 INT, Col2 INT)
INSERT INTO tblTest
SELECT 1, 1
UNION ALL
SELECT 1, 1 –duplicate
UNION ALL
SELECT 1, 1 –duplicate
UNION ALL
SELECT 1, 2
UNION ALL
SELECT 1, 2 –duplicate
UNION ALL
SELECT 1, 3
UNION ALL
SELECT 1, 4
#################################################################
Solution 1 : (Solution for removing duplicate rows from a table which has no identity key)
WITH CTE (COl1,Col2, DuplicateCount)
AS
(
SELECT COl1,Col2,
ROW_NUMBER() OVER(PARTITION BY COl1,Col2 ORDER BY Col2) AS DuplicateCount
FROM tblTest
)
DELETE
FROM CTE
WHERE DuplicateCount > 1
——————————————————————————————————————
Solution 2 : (Solution for removing duplicate rows from a table which has identity key)
SELECT DISTINCT *INTO duplicate_table FROM tblTest
GROUP BY key_value
HAVING COUNT(key_value) > 1
DELETE original_table
WHERE key_value
IN (SELECT key_value
FROM duplicate_table)
INSERT original_table
SELECT *
FROM duplicate_table
DROP TABLE duplicate_table
select row_number() from tblTest
References :
http://blog.sqlauthority.com/2007/03/01/sql-server-delete-duplicate-records-rows/
