Rahul's space

# 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/

http://support.microsoft.com/kb/70956