SQL Server delete duplicate records in table

Duplicate records: Duplicate records in two meanings

The first is a completely duplicate record, that is, all the fields are duplicated records;

Second, some of the key fields are duplicated records, such as the Name field is repeated, and the other fields do not necessarily repeat or repeat can be ignored.

1, for the first kind of repetition, easier to solve, using

1 Select distinct * from TableName

You can get a result set with no duplicate records.

If the table needs to delete duplicate records (duplicate records retain 1), you can delete them as follows

1  --query a result set with no duplicate records and save the result set to a temporary table #tmp2  Select distinct *  into#Tmp fromTableName3  --Delete Table TableName4  Drop TableTableName5  --Insert temporary table #tmp data all into the table tablename6  Select *  intoTableName from#Tmp7  --Delete temporary table8  Drop Table#Tmp

This duplication occurs because the table is poorly designed and the unique index columns are added to resolve.

2, this kind of repetition problem usually requires to keep the first record in the duplicate record, the operation method is as follows

Suppose there is a duplicate field name,address, which requires the result set to be unique for both fields

1  --inserts all records from TableName into the temporary table #tmp and increments the line number field2  Select Identity(int,1,1) asAutoid,*  into#Tmp fromTableName3  --Insert no duplicate records from temporary table #tmp into the temporary table #tmp2 (min (autoid) ...) group by name,autoid-implements the first article in a record that retains Name duplicates)4  Select min(autoid) asAutoid into#Tmp2 from#TmpGroup  byname,autoid

The last select is the result set that name,address not duplicate (but one more autoid field, which can be written in the SELECT clause without this column in the actual write)

By using the above operation and saving the TableName result set to the temporary table #tmp2, you can delete the temporary table #tmp2 records by deleting the table tablename and then inserting the TableName implementation.

Duplicate records in TableName (only the first record of duplicate records is retained)

Note:There is no autoid field in TableName, you can select to remove the Autoid field implementation

SQL Server delete duplicate records in table