Summary of methods for deleting duplicate records on SQL Server 2008

Method 1,

This is a primary method for deleting duplicate records. We use distinct to store unique data in a table.

The code is as follows:	Copy code
Select distinct * into # tmp from tablename

Then, delete the original table.

The code is as follows:	Copy code
Drop table tablename

Create a table with the same name as the original table, and then use select into to store the tmp table to the previous table.

The code is as follows:	Copy code
Select * into tablename from # tmp

Delete the old table

The code is as follows:	Copy code
Drop table # tmp

This method is simple, but we cannot use this method if you only query records that are not repeated. You can use other methods.

Another way is to query a Noun sentence

 

The code is as follows:	Copy code
WITH Dups ( Select [ID], [Name], [Age], [Sex] , ROW_NUMBER () OVER (Partition By [Name] Order By (SELECT 0) AS rn , RANK () OVER (Partition By [Name] Order By (SELECT 0) AS rnk FROM Employee ) Delete from Dups WHERE rn <> rnk;

I have tested all the above methods, and they are all good, but baidu's good practice later

 

ProductID	ProductName	Unit	UnitPrice
1	North	Bottole	9.0
2	Chang	Bottole	20.0
2	Chang	Bottole	20.0
3	Anistreed	G	1.0
4	Thu	Kg	2.8
4	Thu	Kg	2.8
4	Thu	Kg	2.8

 The records of product Chang and Tofu are repeated in the product information table. To delete these duplicate records, only one of them is retained.

Purpose: to delete duplicate rows in a table. Only one record is left for each Product. Assume that the table name is TEST.

Step 1: Add an ID column for the original table.
           

The code is as follows:	Copy code
Alter Table TEST Add Id int Identity (1, 1)

In this way, the table data is processed as follows:
          

ID	ProductID	ProductName	Unit	UnitPrice
1	1	North	Bottole	9.0
2	2	Chang	Bottole	20.0
3	2	Chang	Bottole	20.0
4	3	Anistreed	G	1.0
5	4	Thu	Kg	2.8
6	4	Thu	Kg	2.8
7	4	Thu	Kg	2.8

Step 2: run the following SQL statement.

The code is as follows:	Copy code
Delete from TEST Where ID Not In (Select MIN (ID) From TEST Group by ProdutyID)