How to quickly delete duplicate records in SQL Server

How to quickly delete duplicate records in SQL Server

Developer nightmare-delete Repeated Records

Presumably, every developer has had a similar experience. When querying or collecting statistics on the database, the query and statistics results may be inaccurate due to repeated records in the table. The solution to this problem is to delete these duplicate records and keep only one of them.

In SQL Server, apart from manual deletion of tables with more than a dozen records, duplicate deletion records are generally writtenCode, Use the cursor method to check a row and delete duplicate records. Because this method needs to traverse the entire table, it is feasible if the number of records in the table is not large. If the number of data records in a table reaches millions, it is a nightmare to use the cursor method to delete it, because it will be executed for a long period of time.

Four axes-easily eliminate repeated records

I do not know that there is a simpler method in SQL Server. It does not need to use a cursor. You only need to write a simple insert statement to delete duplicate records. To make it clear, we first assume that there is a product information table products. Its table structure is as follows:

Create Table products (
Productid int,
Productname nvarchar (40 ),
Unit char (2 ),
Unitprice money
)
 

Table data 1:

As shown in figure 1, 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. The procedure is as follows:

The first axe -- create a temporary table with the same structure

Create Table products_temp (
Productid int,
Productname nvarchar (40 ),
Unit char (2 ),
Unitprice money
)
 

Second Board ax-add an index to the table and make it ignore duplicate values

In Enterprise Manager, find the created temporary table products _ temp, right-click, select all tasks, select Manage index, and choose create. 2.

Set the index option according to the area circled in Figure 2.

The third axe -- copy product information to the temporary table

Insert into products_temp select * from products
 

At this time, SQL Server Returns the following prompt:

Server: Message 3604, level 16, status 1, Row 1
Duplicate keys are ignored.
 

It indicates that no duplicate rows exist in the temporary products_temp table of product information.

Fourth board ax-import new data into the original table

Clear the original product information table products, import data from the temporary table products_temp, and delete the temporary table.

Products_temp.
Delete Products
Insert into products select * From products_temp
Drop table products_temp
 

This completes the deletion of Repeated Records in the table. Regardless of the size of a table, the execution speed of the table is quite fast, and it is safe because there is almost no need to write statements.

tips: deleting duplicate records in the above method depends on the field selected when creating a unique index. In actual operation, the reader must first confirm that the unique index field is created correctly, to avoid deleting useful data.