Developers' Nightmare - Deleting Records I would like to have similar experiences. Every developer has a similar experience. When querying or counting the database, it will encounter a repetitive record in the table from time to time, resulting in the query and statistics. accurate. The way to solve this problem is to delete these repeated records, only one of them. In addition to manually deleting a table with more than ten records in SQL Server, implementing deleting repeated records is generally writing a code, and checks a row with a row with a row, deleting repetitive records. Because this method needs to traverse the entire table, it is still feasible when the number of records in the table is not very large. If a table's data reaches millions, it is a nightmare for the way to use the cursor, because It will perform a considerable period of time. Four-plate ax - Easy to eliminate repeated records, I don't know if there is a simpler method in SQL Server. It doesn't need a cursor. You can implement the deletion of repeated records as long as you write a simple insert statement. In order to clearly express it, we first assume that there is a product information table products, and its surface structure is as follows:
Create Table Products (ProductID INT, ProductName Nvarchar (40), Unit Char (2), Unitprice Money) Data in the table is shown in Figure 1:
As can be seen in Figure 1, the record of the product Chang and Tofu is repeated in the product information table. Now delete these repeated records, only one of them. The steps are as follows: First board ax - establish a temporary table with the same structure
CREATE TABLE PRODUCTS_TEMP (ProductID INT, ProductName NVARCHAR (40), Unit Char (2), Unitprice Money) Second-ax / ax - add index for this table, and ignore the repeated value method to find above in Enterprise Manager Established temporary table products _Temp, right-click, select all tasks, select management index, select New. as shown in picture 2. Set the index options as shown in Figure 2.
Third board ax - copy product information to temporary table
Insert INTO PRODUCTS_TEMP SELECT * AROM PROMTUCTS This time SQL Server returns the following prompt: Server: Message 3604, Level 16, State 1, Row 1 has ignored the repeated key. It indicates that there is no repetitive row in product information temporary table products_temp. Fourth Ax - Import the new data into the original table to empty the original product information table Products, and import the temporary table products_temp and finally delete the temporary table products_temp.
DELETE ProductsInsert Into Products Select * from produter_tempdrop Table Products_Temp This completes the deletion of repeated records in the table. Regardless of the table, its execution speed is quite fast, and because it is hard to write a statement, it is also safe. Tip: Deleting Repeated Records in the above method depends on the field selected when the unique index is created. In the actual operation, the reader must first confirm if the created unique index field is correct, so as not to remove useful data.
