Identify and delete duplicate records in a table – SQL

One of the best option is to use CTE, ROW_NUMBER() to identify and delete duplicate records

Sample Code/Demo:

CREATE TABLE Dup_Table(ID int identity(1,1), Name varchar(100), DateCreated datetime)

insert into Dup_Table values('Raj', getdate()) 
insert into Dup_Table values('Bob', getdate()) 
insert into Dup_Table values('Raj', getdate()) 
insert into Dup_Table values('Vijred', getdate()) 
insert into Dup_Table values('John', getdate()) 
insert into Dup_Table values('Ming', getdate()) 
insert into Dup_Table values('Bob', getdate()) 
insert into Dup_Table values('Harit', getdate()) 
insert into Dup_Table values('Raj', getdate()) 
insert into Dup_Table values('Vijred', getdate()) 
insert into Dup_Table values('Ming', getdate()) 
insert into Dup_Table values('John', getdate()) 
insert into Dup_Table values('king', getdate()) 
insert into Dup_Table values('Vijred', getdate()) 


select * from Dup_Table

-- To Identify all the duplicate records
;WITH CTETable as
(	select ID, Name, DateCreated, ROW_NUMBER() 
		OVER(PARTITION BY  Name  -- Include multiple columns 
		ORDER BY DateCreated) AS rownum
		from Dup_Table (nolock)
)
select * from CTETable 
	where rownum > 1 

-- To delete all duplicate records 
;WITH CTETable as
(	select ID , ROW_NUMBER() 
		OVER(PARTITION BY  Name ORDER BY DateCreated) AS rownum
		from Dup_Table (nolock)
)
delete from  CTETable
		where rownum > 1

-- Check for duplicate recoreds		
select * from Dup_Table

-- Clean up
drop table Dup_Table

Note: If one of the column is of type NText, will not be able to sort based on the column and it fails. Convert the column to nvarchar convert(varchar(2000),Course_Notes__c) as a work around.

-Vijred

Advertisements
This entry was posted in Productivity, Script, SQL, SQL Query and tagged , , , , , . Bookmark the permalink.

One Response to Identify and delete duplicate records in a table – SQL

  1. guest says:

    Delete Duplicates with this query:

    ;WITH CTE AS
    (select Name, min(Dup_Table.ID) as minid, count(*) as count from Dup_Table
    group by Name having count(*) > 1
    )
    delete d
    from CTE C
    join Dup_Table d on d.ID c.minid and d.Name = C.Name

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