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]