This is a very vague question that could get a really complicated solution. If you simply want to locate a duplicate row then you will need to use something like:
resolving the duplicates will be a whole new piece of work
answered May 18 '10 at 07:43 AM
There are a number of ways to solve this using TSQL. The best these days seem to revolve around using ROW_NUMBER(). The key is to simply understand the basic concept that you need a method to uniquely identify the row. Then you need a way to mark duplicate values for that unique identifier and then you need a mechanism to remove those duplicates. While this sounds like three steps, you should be able to do all this in a single query.
answered May 18 '10 at 08:38 AM
Grant Fritchey ♦♦
I hope that I understand the question correctly. The task is to find the duplicate records across all columns in the table. I will also provide the sample of how to quickly delete all such duplicates. Lets create a heap table and insert some records in it (including some duplicates:
Now we have 4 occurences of (1, 1); 2 occurences of (2, 5); (3, 1) does not have any duplicates and we also have 2 occurences of (4, 6). Here is the script to quickly identify all the duplicates:
Here is the result of the query above:
Suppose we want to get rid of all dups while preserving all unique rows. In other words, the end result is expected to have #t with one (1, 1) record, one (2, 5) record, one (3, 1) record, and one (4, 6) record,. The statement to do this can be like this:
The above will delete all dups preserving the unique records only.
answered May 19 '10 at 01:02 PM