Remove duplicate records from SQL Server 2005


WITH mycte AS

(

SELECT *, ROW_NUMBER() OVER (partition BY ID, colName

ORDER BY ID) AS num

FROM

tablename)

SELECT

* FROM mycte WHERE num > 1

–delete from mycte WHERE num > 1

You can delete the duplicates by uncommenting the above line.

Advertisements


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