6 ways SQL Server removes duplicate rows

SQL Server deduplication is one of our most common actions, and here's a look at six different ways SQL Server can remove duplicate rows for your reference.

1. If there is an ID field, it is a unique field

delect table TableName where ID not in (the Select Max (ID) from the table group by Col1,col2,col3:   . )   

The field followed by the GROUP BY clause is the condition you use to judge the repetition, such as only col1, so that the record is the same as long as the col1 field has the same contents.

2. If you are judging all fields you can do the same, "check whether the specified fields in the table are the same"

SELECT * into #temp from tablename GROUP by Id1,id2,....

Delete TableName

INSERT INTO table select * FROM #temp

drop table #temp

3. First to repeat, then get n*1 data inserted into the temporary table, "for all fields in the table is checked for the same", then the data of the original table is deleted, then the temporary table data inserted into the original table, and finally delete the temporary table.

SELECT DISTINCT * to #temp from TableName

Delete TableName

Go

Insert TableName SELECT * FROM #temp

Go

drop table #temp

4. Case without ID

Select Identity (int,1,1) as id,* into #temp from tabel

Delect # where ID not in (

Select Max (ID) from # Group by col1,col2,col3 ...)

Delect table

Inset into table (...)

Select ... from #temp

5. col1+ ', ' +col2+ ', ' ... col5 Federated primary Key

SELECT * FROM table where col1+ ', ' +col2+ ', ' ... col5 in (

Select Max (col1+ ', ' +col2+ ', ' ... col5) from table

where having Count (*)>1

GROUP BY Col1,col2,col3,col4

)

The field followed by the GROUP BY clause is the condition you use to judge the repetition, such as only col1, so that the record is the same as long as the col1 field has the same contents.

6.

Select Identity (int,1,1) as id,* into #temp from tabel

SELECT * from #temp where ID in (

Select Max (ID) from #emp where have count (*)>1 GROUP by col1,col2,col3 ...)

6 ways SQL Server removes duplicate rows