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 )

　


product Chang and tofu records are duplicated in the product information table. Now you want to delete these duplicate records and keep only one of them. Steps 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-indexes the table and ignores duplicate values

The
method is to locate the temporary table created above in Enterprise Manager products _temp, right-click, select All Tasks, select the management index, select a new index, select unique value, and select Ignore duplicate values.





Third Axe--copy product information to temporary table

INSERT INTO Products_temp Select *

at this point SQL Server returns the following prompt:


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


The duplicate key has been ignored.


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


Fourth Axe--import new data into the original table


the original Product information table products empty, and the temporary table products_temp data import, and finally delete temporary table products_temp.

Delete Products INSERT into the 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.