Find and delete duplicates - T-SQL

Taewook Kang

Duplicates are annoying. It happens because of poorly designed insertion process. Before using the following queries, you should examine how duplicates were created in your table in the first place.

Here is how you select duplicates in [field1] column:

select field1, count(field1) as counted
	from table1
		group by field1
			having (count(field1)>1)

There are many ways to remove duplicates, but the following is what I prefer:

-- first create a new table
create table [duplicate_free_table] (
	sysid 				int, 
	field1				varchar(100), 
	field2				varchar(100),
	field3				varchar(100)

-- then create an index on fields with IGNORE_DUP_KEY option set!
create unique index [duplicate_free_1] on 
	[duplicate_free_table] (field1, field2, field3) with IGNORE_DUP_KEY

-- when rows are copied, duplicates will be ignored due to the previously created index
insert [duplicate_free_table] 
	select * from [table_with_duplicates]