Detecting and Deleting Duplicate Records in SQL Server 2005 and Above

Submitted by tamizhventhan on Thursday, September 5, 2013 - 20:49.

Language

Most of the time, we encounter duplicate records in our database. And it's not easy to delete it if you don't have enough knowledge about SQL Statement. This code will help you detect and delete duplicate records in SQL Server 2005 and above. (These commands only works on sql server 2005 or higher)

Create table

CREATE TABLE example(id INT IDENTITY(1,1),name VARCHAR(20),class VARCHAR(20));

(Here Identity is used for auto increment of values starting from 1)

Insert Records

 
INSERT INTO example(name,class)VALUES('tam','1');
INSERT INTO example(name,class)VALUES('tam','1');
INSERT INTO example(name,class)VALUES('ram','1');
INSERT INTO example(name,class)VALUES('tam','2');
INSERT INTO example(name,class)VALUES('tam','1');
INSERT INTO example(name,class)VALUES('ram','1');
INSERT INTO example(name,class)VALUES('ram','2');
INSERT INTO example(name,class)VALUES('ram','2');

Here we are inserting records. It contains duplicates also. For the detection and Deletion of duplicates we use Common Table Expressions (CTE).

Detecting Duplicates

SELECT ROW_NUMBER() OVER(partition BY name,class ORDER BY id) AS dup FROM example;

If you get more than 1 in any of the columns, that means you have duplicate records in your Table.

Deleting Duplicates

WITH sample1
AS
(SELECT ROW_NUMBER() OVER(partition BY name,class ORDER BY id) AS dup FROM example)
DELETE FROM sample1 WHERE dup>1
GO

With this code snippet you can delete duplicates from your table. (Here Sample1 is any name, it is a CTE name).

Note: Due to the size or complexity of this submission, the author has submitted it as a .zip file to shorten your download time. After downloading it, you will need a program like Winzip to decompress it.

Virus note: All files are scanned once-a-day by SourceCodester.com for viruses, but new viruses come out every day, so no prevention program can catch 100% of them.

FOR YOUR OWN SAFETY, PLEASE:

1. Re-scan downloaded files using your personal virus checker before using it.
2. NEVER, EVER run compiled files (.exe's, .ocx's, .dll's etc.)--only run source code.

Add new comment

305 views