Sqlserver Delete duplicate data

I. Filtering duplicate data

1. Completely Repeated Records

 
Select DistinctField 1, Field 2, Field 3FromData Table
 

2. Record with duplicate key fields

 
/*Data Structure: Role file (role encoding, role, and role classification encoding) function: retrieve the non-duplicated data with the specified field (role classification encoding) as the keyword. The first repeated description is as follows: repeat the record to get the last one. You only need to change min to Max.*/ Select * FromRole file tWhereRole codeIn(Select Min(Role code)FromRole file T1Group ByT1. role classification code)
 

 

Ii. Delete duplicate records

During database usageProgramThis section describes how to delete duplicate data.

 

There are two Repeated Records. One is a completely repeated record, that is, records with all fields being repeated, and the other is records with duplicate key fields, such as duplicate name fields, other fields are not necessarily repeated or can be ignored.

1. For the first type of repetition, it is easier to solve.

 
Select Distinct * FromTablename
 

 

You can get the result set without repeated records.

If the table needs to delete duplicate records (one record is retained), you can delete the record as follows:

Select Distinct * Into# TMPFromTablenameDrop TableTablenameSelect * IntoTablenameFrom# TMPDrop Table# TMP
 

 

The reason for this repetition is that the table design is not weekly. You can add a unique index column.

2. Repeat problems usually require that the first record in the repeat record be retained. The procedure is as follows:

Assume that the duplicate fields are Name and address. You must obtain the unique result set of the two fields.

 Select  Identity ( Int , 1 , 1 ) As Autoid, *   Into # TMP From  Tablename  Select   Min (Autoid) As Autoid Into # Tmp2 From # TMP Group   By  Name, autoid  Select   *   From # TMP Where Autoid In ( Select Autoid From # Tmp2)

 

The last SELECT command gets the result set with no duplicate name and address (but an autoid field is added, which can be omitted in the select clause when writing)

 

My demo:

 Select   Identity (Int , 1 , 1 ) As  Autoid,  [  Productcode  ] , [  Categoryid  ]   Into # TMP From  DBO. tblproduct  Select  Min (Autoid) As Autoid Into # Tmp2 From # TMP Group   By  Productcode, companyid  Truncate   Table  DBO. tblproduct  Insert   Into Tblproduct ( [  Productcode  ] ,[  Categoryid  ]  )  Select   [  Productcode  ] , [  Categoryid  ]         From  # TMP  Where Autoid In ( Select AutoidFrom  # Tmp2)  Select   Count ( * ) From  # TMP  Select   Count ( * ) From  # Tmp2  Drop   Table  # TMP  Drop  Table # Tmp2

 

 

 

Original article: http://fdsazi.blog.51cto.com/1871366/375949/