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