How to Find Duplicate Records in a Database
If you've accidentally imported duplicate records into a database, you can use the SQL Server console to run statements to find them. Duplicate records in a database take up space and can cause data integrity problems for even small applications. Using the console, you can run select statements that find all the duplicate records in a table. The one requirement is that you have an identifiable field that can be used to detect when two records are duplicates. This field is determined by your database administrator or system developers.
Other People Are Reading
Instructions
-
-
1
Open the SQL Server Management console. The shortcut for this program is usually found in the "SQL Server" program directory in your Windows Start menu.
-
2
Login to the SQL Server using a valid user name and password. The user name you use needs to have "select" rights on the table.
-
-
3
Decide what criteria you want to use to indicate a duplicate record. For instance, if you are looking for duplicate contact information in a table, you can use phone number, social security, email or any other identifiable information that is unique to one person. The name is not normally used, because several people in your database may have the same full name. For this example, the phone number field in the table is used.
-
4
Enter the following code into the console's text window:
SELECT phone,
COUNT(phone) AS Duplicates
FROM customer
GROUP BY phone
HAVING ( COUNT(phone) > 1 )
In this example, the query only shows you records in the table that contain the same phone number. -
5
Press the F5 key. This runs the query. The results shown in the bottom window pane are all the records that contain at least one duplicate.
-
1
