SQL Server 2000 deletes duplicate records from a table

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 delete data by using a cursor, because it will take a long time.

In SQL Server 2000, how does one Delete repeated records in a table (records are identical before Repeated Records )?

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 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.

 

 

 

If the record is identical, the record is repeated. (The test passes in SQL Server)

Select distinct * into # tmpp from tid
Delete from tid
Insert into tid select * from # tmpp
Drop table # tmpp

If there is an id Primary Key (number, auto-increment of 1), then (the test passes in SQL server2000)

Delete from tableA where id not in
(Select id = min (id) from tableA group by name)