Using SQL Query to Find Duplicate Records

Use the following SQL query to list the keys and the record counts for duplicate records in a table. Duplicate records result when the given combination of keys is used as the index. This query is also useful when trying to determine if certain key combinations produce a unique key.

SELECT key_1, key_2,...key_n, COUNT(*) FROM tablename

GROUP BY key_1, key_2,...key_n

HAVING COUNT(*) >1

Where

Is

key_1
key_2
key_n

The list of columns that make up the index for the table.

tablename

The name of the table for which the error occurs.

If no records are duplicated, the output within the relational database query tool will indicate that no rows have been affected. If the SQL query returns a GROUP of duplicates, do the following:

  1. Manually delete the extra record or records for each combination of duplicate records.
  2. Execute a dbutility runscript command for each table that contained duplicate records, specifying the index creation script as follows:

    dbutility -n runscript dbscripts\script.index.tablename

  3. Set ds_mode = 1 for each data set that contained duplicate records.
  4. Execute a dbutility process command.

    Note: If the query routine returns an unusually high number of duplicates, there may be more serious problems with your keys or the process that creates them. For more information about how DATABridge uses keys, see Creating Indexes for Tables.