Scenario 1, one record is completely identical to another
Meaning that there is no way of identifying them who is who. Usually, it happens when the table doesn’t have a unique id field due to the poor database design. Because of that, there is no way in SQL to delete one of these duplications without deleting them all first.
You can copy the whole table into a new table first,
SELECT DISTINCT * INTO newTable FROM oldTable
And then, delete the old table and move all the records back but this time make sure you have the unique identifier set up as one field.
Scenario 2, similar records
The records have one field that is the same each other and only one copy needs to be preserved. Usually, they have the unique identifier set up to identify one to another. Then, try
DELETE t1 FROM oldTable AS t1, oldTable AS t2
WHERE (t1.duplicateField = t2.duplicateField )
AND (t1.uniqueidentifier > t2.uniqueidentifier)
As a side note, if you run into a program in which you keep getting message telling you that
If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required.
that’s because one of the fields has the same name reserved as keywords in SQL server. You should put “[“ and “]†around it to tell the server that this is not a reserved keyword.