How to quickly delete duplicate records in SQL Server

Nightmare for Developers--delete duplicate records

Presumably every developer has had a similar experience, and when querying or counting the database, the query and statistic results are inaccurate due to duplicate records in the table. The solution to this problem is to delete the duplicate records and keep only one of them.

In SQL Server, in addition to manually deleting a table with more than 10 records, implementing a delete duplicate is typically a piece of code that is checked by a row of cursor methods, deleting duplicate records. Because this method requires traversal of the entire table, it is still possible for the number of records in the table to be very large, and if the data for a table is up to millions, it is a nightmare to delete with the cursor method because it will take a long time to execute.

Four axe--easy to eliminate duplicate records

There is a simpler way to do this in SQL Server, which does not require cursors, as long as a simple INSERT statement can be used to remove duplicate records. In order to be clearly stated, we first assume that there is a product information table products, whose table structure is as follows:

CREATE TABLE Products (
ProductID int,
ProductName nvarchar (40),
Unit char(2),
UnitPrice money
)

The data in the table is shown in Figure 1:

As you can see in Figure 1, the records of the product Chang and tofu are duplicated in the product information table. Now you want to delete these duplicate records and keep only one of them. The steps are as follows:

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 Axe--index the table and ignore duplicate values

The method is to find the temporary table created above in Enterprise Manager products _temp, right-click, select All Tasks, select the management index, select New. As shown in Figure 2.

Set indexing options in the places that are circled in Figure 2.

Third Axe--copy product information to temporary table

insert into Products_temp Select * from Products

SQL Server now returns the following prompt:

Server: Message 3604, Level 16, State 1, line 1

Duplicate keys have been ignored.

It indicates that no duplicate rows appear in the products_temp of the Product information temporary table.

IV Axe--importing new data into the original table

Empty the original product information sheet and import the data from the temporary table products_temp, and then delete the temporary table products_temp.

delete Products
insert into Products select * from Products_temp
drop table Products_temp

This completes the deletion of duplicate records in the table. Regardless of the size of the table, its execution speed is fairly fast, and because it is almost impossible to write a statement, it is also safe.

Tip: Deleting duplicate records in the above method depends on the field you select when you create a unique index, and in the actual course of action you must first confirm that the unique indexed field you created is correct to avoid deleting useful data.