To remove duplicate rows in sql select statement

This article provides a script that you can use to remove duplicate rows from a table in Microsoft SQL Server.

Original product version:   SQL Server
Original KB number:   70956

Summary

There are two common methods that you can use to delete duplicate records from a SQL Server table.

For demonstration, start by creating a sample table and data:

Then, try the following methods to remove the duplicate rows from the table.

Method 1

Run the following script:

This script takes the following actions in the given order:

• Moves one instance of any duplicate row in the original table to a duplicate table.
• Deletes all rows from the original table that are also located in the duplicate table.
• Moves the rows in the duplicate table back into the original table.
• Drops the duplicate table.

This method is simple.

However, it requires you to have sufficient space available in the database to temporarily build the duplicate table. This method also incurs overhead because you are moving the data.

Also, if yo

how to remove duplicate rows in sql based on two columns

how to remove duplicate rows in sql select query