How to delete duplicate lines in SQL Server

Today, I will talk to you about how to delete duplicate lines in SQL Server. Many people may not know much about it. In order to let everyone know more, Xiaobian summarizes the following contents for everyone. I hope you can gain something according to this article.

1. Delect table where id not in ( select max(id) from table group by col1, col2, col3... )

The field following the group by clause is the condition you use to determine duplication. For example, if there is only col1, then as long as the contents of the col1 field are the same, it means that the records are the same. 2. If yes, all fields can also be copied like this: select * into #aa from table group by id1, id2,... delete table insert into table select * from #aa

3. The copy code for the case without ID is as follows: select identity(int,1,1) as id,* into #temp from tabel select # where id not in ( select max(id) from # group by col1, col2, col3...) delect table inset into table(...) select ..... from #temp

4. col1+','+col2+','... col5 The union primary key copy code is as follows: 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 following the group by clause is the condition you use to determine duplication. For example, if there is only col1, then as long as the contents of the col1 field are the same, it means that the records are the same. 5. The copy code is as follows: 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...)

6. The copy code is as follows: select distinct * into #temp from tablename delete tablename go insert tablename select * from #temp Sqlclub go drop table #temp

After reading the above, do you have any further understanding of how to delete duplicate rows in SQL Server? If you still want to know more knowledge or related content, please pay attention to the industry information channel, thank you for your support.