Home > Sql Server > SQL SERVER – Delete Duplicate Records – Rows

SQL SERVER – Delete Duplicate Records – Rows


# 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

Advertisements
Categories: Sql Server
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: