Quickly delete duplicate records in SQL Server

Source: Internet
Author: User

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, deleting duplicate records usually involves writing a piece of code and performing a row-by-row check using the cursor method, 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
)

 
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 the Enterprise Manager, find the created temporary table Products _ temp, right-click, select all tasks, select Manage index, create an index, and select "unique value ", select ignore repeated values ".

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 a unique index is created. In actual operation, the reader must first confirm that the unique index field is correct, to avoid deleting useful data.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.