T-SQL Delete duplicate data

There are two types of data duplication: One is a complete repetition of the same for each field, and the second is a duplicate result set for a partial field. For example, the Name field repeats, and the other fields are not necessarily duplicates or can be omitted.

The first case is easier to solve, and the result set without duplicates can be obtained using SELECT DISTINCT * FROM TableName.

If the table needs to delete duplicate records (duplicate records keep one), you can delete them by clicking the following method:

Select distinct *  into  from TableName Drop Table TableName Select *  into  from #Tmp Drop Table #Tmp

2. The second type of repeating problem usually requires the first record in the record to be retained, as follows:

--Suppose there is a duplicate field name,address, which requires the result set to be unique for both fieldsSelect Identity(int,1,1) asAutoid,*  into#Tmp fromTableNameSelect min(autoid) asAutoid into#Tmp2 from#TmpGroup  byname,autoidSelect *  from#TmpwhereAutoidinch(SelectAutoid from#tmp2)

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)

Method Two

Declare @max integer,@id integerDeclareCur_rowscursorLocal for SelectMain field,Count(*) fromTable nameGroup  byMain field having Count(*)> 1Opencur_rowsFetchCur_rows into @id,@max while @ @fetch_status=0beginSelect @max = @max -1Set RowCount @maxDelete  fromTable namewhereMain field= @idFetchCur_rows into @id,@maxEndClosecur_rowsSet RowCount 0

T-SQL Delete duplicate data