Quickly delete duplicate records in SQL Server

Quickly delete duplicate records in SQL Server 2006-07-17 21:53:15

Category: SQL Server

Developer's Nightmare-delete duplicate records Presumably, every developer has had a similar experience, when querying or statistics of the database will occasionally encounter due to the existence of duplicate records in the table resulting in inaccurate query and statistical results. The solution to this problem is to delete the duplicate records and keep only one of them. In addition to manually deleting a table with more than 10 records in SQL Server, implementing delete duplicates is generally a piece of code, using a cursor method to check one line at a time to delete duplicate records. Because this method needs to traverse the entire table, it is still possible for the number of records in the table to be very large, and if the data of a table is up to millions, it is a nightmare to remove it with a cursor because it will take a long time to execute. 　　 four axe--easily eliminate duplicate records But in SQL Server there is a more simple method, it does not need to use a cursor, as long as the write a simple INSERT statement can be implemented to delete the duplicate record function. In order to be able to express clearly, we first assume that there is a product information table products, its table structure is as follows: CREATE TABLE Products (ProductID int, ProductName nvarchar (+), Unit char (2), UnitPrice money) It is assumed that the records of the product Chang and tofu are duplicated in the product information table. Now to delete these duplicate records, keep only one of them. The steps are as follows: 　　 first axe-creating a temporary table with the same structure CREATE TABLE products_temp (ProductID int, ProductName nvarchar (+), Unit char (2), UnitPrice money) 　　 second Axe--indexes the table and ignores duplicate values The method is to find the temporary table created above in Enterprise Manager products _temp, right-click, select All Tasks, select Manage Index, select New. Then set the indexing options. 　　 Third Axe--copy product information to temporary table INSERT INTO Products_temp Select * FROM Products SQL Server will now return the following prompt: Server: Msg 3604, Level 16, State 1, line 1 The duplicate key has been ignored. It indicates that no duplicate rows appear in the Product information temporary table products_temp. 　　 Fourth Axe--import new data into the original table Empty the original product information sheet products, import the data from the temporary table products_temp, and finally delete the temporary table products_temp. Delete Productsinsert into products select * from Products_tempdrop table products_temp This completes the deletion of duplicate records in the table. Regardless of the size of the table, it executes fairly quickly, and because it is almost not written, it is safe. Tip: Deleting duplicate records in the above method depends on the field you select when creating a unique index, and in the actual operation, the reader must first confirm that the unique indexed field was created correctly to avoid deleting the useful data.

[@[email protected]]

Quickly delete duplicate records in SQL Server