how to delete duplications permanantly from table using sql server7.0

[shree]

hi all,

i have one table. at the time of transactions some duplications are inserting in the database.
i want to delete duplications but not distinct rows. Means i want only distnict rows in the database.
how it is possible using sql server7.0.

i have one idea. At first i want to create a Alias of particular table. i will shift rows to the alias table. after that i will delete origional.
is it works???

if you find answer about this please.... reply.

thank you advancedly
shree

[raf]

- copy the structure of your table (structur, without data. or if you don't know how to do this, copy table and delete all records)
- create a unique index (thats an index that doesn't allow duplicate values) on the field(s) that should be unique (combinations)
- run an appendquery to insert all records from your original table into this new table --> the duplicate records will not be inserted. Only the first of the duplicate records will be inserted.
- delete your original table
- rename your new table to the original tables name

[shree]

hi

thank you once again.

and i have one more doubt. please..

i want to see the duplication records only. How to write a query to this problem using sqlserver 7.0

thank you. iam really appreciating you because of your helping.

sincerely
rajashree

[raf]

you can do that by running a count on the (combination of) column(s) that should be unique --> the ones that you would create your unique index on.

like
SELECT COUNT(*), column1, column2 FROM yourtable GROUP BY column1, column2

all records with a count higher then 1 are duplicates. You can create a temporarely table of this, or use it as a subquery to get all info from the duplicated records out of the original table.