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)
(Here Identity is used for auto increment of values starting from 1)
Here we are inserting records. It contains duplicates also.
For the detection and Deletion of duplicates we use Common Table Expressions (CTE).
If you get more than 1 in any of the columns, that means you have duplicate records in your Table.
With this code snippet you can delete duplicates from your table.
(Here Sample1 is any name, it is a CTE name).
Create table
- CREATE TABLE example(id INT IDENTITY(1,1),name VARCHAR(20),class VARCHAR(20));
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');
Detecting Duplicates
- SELECT ROW_NUMBER() OVER(partition BY name,class ORDER BY id) AS dup FROM example;
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
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
- 300 views