6 ways SQL Server removes duplicate rows

Source: Internet
Author: User
Tags repetition

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.