Deleting duplicate rows on SQL Server is one of the most common operations. The following describes six methods that can be used to delete duplicate rows on SQL Server for your reference.
1. If an ID field exists, it is a unique field.
Delect table tablename where id not in (select max (ID) from Table group by col1, col2, col3...)
The field followed by the group by clause is the condition for you to judge repetition. For example, if only col1 is used, if the content of col1 is the same, the record is the same.
2. If you want to determine all fields, you can also [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, repeat and obtain N * 1 pieces of data and insert it into the temporary table. [check whether all fields in the table are the same.] Then, delete the data in the original table, insert the data in the temporary table to the original table, and delete the temporary table.
Select distinct * into # temp from tablename
Delete tablename
Go
Insert tablename select * from # temp
Go
Drop table # temp
4. No 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 join 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 for you to judge repetition. For example, if only col1 is used, if the content of col1 is the same, the record is the same.
6.
Select Identity (INT, 1, 1) as ID, * into # temp from tabel
Select * from # temp where ID in (
Select max (ID) from # EMP where having count (*)> 1 group by col1, col2, col3 ...)