Find duplicates 2

[wolfert]

Hello,

I have imported an access dbase into SQL 2k. Afterwards I have done some querying / updating etc.
The problem is that somehow there are a lot of duplicate records in the table. I most have made a mistake with updating the table before.
I have tried linking the table in an access dbase and use the find duplicate query wizard in access but somehow this doesn't work for sql-server tables. Must has got something to do with the sql-syntax.

Is there any other way to remove duplicates straigh from either the enterprise manager or query analyser? Pherhaps a 3rd party tool?

Thanks,
Patrick.

 

[MaffewW]

theres various ways depending on exactly what data is duplicated.

faq183-2682 has a useful link. Also some people choose to do it by using an identity (autonumber in access) column and useing group bys. or if they are exact duplicates (if you havent got a primary key set yet(which you should really))

select distinct * into newtablename

then truncate the original table and import the data back in

Matt

Brighton, UK

 

[wolfert]

Thanks. Works like a charm.

I will set the primary key it's just that I just finished importing the table from access.

patrick

 

[InquisitorOthello]

everytime I do a query "select distinct * from [database] INTO [new table]" I get an error "Syntax Error near INTO", even if I put the new table name in single quotes.

I can do a DTS file transfer that uses a select distinct *, but that's it.

 

[olichap]

InquisitorOthello, put the INTO statement in front of the FROM

Select * into [tbl name] from ......