SYMPTOMS
Duplicate rows may be inserted into a table by a bulk copy operation or a Data Transformation Services (DTS) import package under the following conditions:
- The target table does not have a clustered index and has a unique nonclustered index created with the ignore_dup_key option.
- The input file contains duplicate rows.
- The Select Into/Bulk Copy option is on.
To verify whether duplicate rows were inserted, compare the number of rows returned from the base table by running the following query:
SELECT COUNT(*) FROM 'table name' (index = 0)
with the number of rows returned using the unique nonclustered index by running the following query:
SELECT COUNT(*) FROM 'table name' (index = 'indid for unique non clustered index')
If there are duplicate rows, the first value will be greater than the second value.
The bulk copy operation will introduce corruption on the index pages of the unique nonclustered index. The following errors will be reported by DBCC CHECKTABLE:
Server: Msg 8951, Level 16, State 1, Line 0
Table Corrupt: Table 'table_name' (ID 1797581442). Missing or invalid key in index 'IDX_2' (ID 2) for the row:
Server: Msg 8955, Level 16, State 1, Line 0
Data row (1:97:0) identified by (RID = (1:97:0) ) has index values (Col1 = 1 and Col2 = 1 and Col3 = 12 and Col4 = Sep 30 2000 12:00AM!t©~0WeG).
NOTE: The bulk copy operation continues inserting data even though it attempted to insert a duplicate row because the unique index was created with the ignore_dup_key option, which issues a warning message (3604 'Duplicate key was ignored') and ignores the row with a duplicate key.
WORKAROUND
For a temporary workaround to clean the target table, follow these steps:
- Drop the unique nonclustered index.
- Remove the duplicate rows by following the instructions in the following article in the Microsoft Knowledge Base:
139444 INF: How to Remove Duplicate Rows from a Table
- Re-create the unique nonclustered index.
To prevent the problem from reoccurring, you can do either of the following:
- Create a clustered index on the target table.
-or-
- Turn off the Select Into/Bulk Copy database option.
NOTE: DBCC CHECKTABLE with any repair option will not correct this problem. Dropping and re-creating the index will also not correct the problem, because the unique nonclustered index cannot be re-created due to duplicate rows in the base table.