Solved: Remove duplicate records

[dicepackage]

I have a table with some duplicate records. The problem is I have to check for more than one field to know if it is truly a duplicate. I need to check if customerID, OrderItem, and SaleDate are all the same. If so I want to remove the duplicates. I drew a table below to try to show what I am talking about. I want to remove one of the Robert records where Widget and SaleDate are duplicated but not the one where the OrderItem and SaleDate change.

CustomerID CustomerName CustomerAddress OrderItem Price QTY SaleDate
1 John 123 Fake Street Doodad 10.00 1 20110101
2 Robert 742 Evergreen Widget 12.50 1 20110202
2 Robert 742 Evergreen Widget 12.50 1 20110202
2 Robert 742 Evergreen Doodad 13.75 1 20110202
3 Igor 1600 Penn Ave doohickey 10.00 1 20110408

[dicepackage]

I was able to solve this by importing the data into a new table called temp. The table temp then had multiple primary keys such that it could not possibly have the same record in all of these combined fields. I then imported the temp table back into the database and was able to get it going duplicate free.

[IrishCharm]

the below should work for you and will only take 2 seconds. Bang everyhting into temp table, truncate original and then isnert distinct values - removes duplicates

select *
into #tmp
from tbl_YOURTABLE

truncate tbl_YOURTABLE

insert into tbl_YOURTABLE
select distinct * from #tmp

[stanl]

I have a table with some duplicate records.

In the first place those fields should not even be in the same table. Generally, you would want 3 tables:

customer: w/primary key id
invoice: with primary key invid, foreign key id
items: primary key itemid, foreign key invid

which makes it hard to generate duplicates in the first place, and time-consuming SQL creating temp tables etc... irrelevant. just .02

[stanl]

I have a table with some duplicate records.

In the first place those fields should not even be in the same table. Generally, you would want 3 tables:

customer: w/primary key id
invoice: with primary key invid, foreign key id
items: primary key itemid, foreign key invid

which makes it hard to generate duplicates in the first place, and time-consuming SQL creating temp tables etc... irrelevant. just .02