CODEWITHSUNDEEP
sqlsql-serverduplicatesprimary-key
Tim Wilcox
Tim Wilcox

Reputation: 1331

SQL Server : Duplicate Primary Key error

I am using SQL Server Express and importing via the Import Wizard so no code to offer really.

The error is shown here:

Error 0xc0202009: Data Flow Task 1: SSIS Error Code DTS_E_OLEDBERROR.
An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "The statement has been terminated.".

An OLE DB record is available.
Source: "Microsoft SQL Server Native Client 11.0"
Hresult: 0x80004005
Description: "Violation of PRIMARY KEY constraint 'PK_CXS'. Cannot insert duplicate key in object 'dbo.cxs'. The duplicate key value is (00, 00, 000000, 2017, 03, 01, 00000000, 0).".

I have since done tests in Excel by filtering by the values given. Only one row comes up. I concatenated the eight columns that represent the primary key and then look for duplicates. None are found. Next, using TextFX tools in Notepad ++, I tried to remove duplicates and none were found.

Any other methods for finding this seemingly nonexistent duplicate?

Upvotes: 0

Views: 7708

Answers (1)

joanolo
joanolo

Reputation: 6328

A few suggestions (that are not really a full answer, but with the given data, is as far as I can go).

In some occasions, you may have data as text (such as 1.000000001 and 1.000000002) that are different in your text files, but become the same once converted into their destination types, because of truncation, rounding, or other kind of conversion effect. This might be the reason why you cannot find duplicates in your text file, yet the database finds them.

There are few techniques you could use and check:

  1. Apart from the imported data ... is there any previous data already on the target table? If so, there you have a possible source for duplicates.

  2. Do you have any way to import "row by row" (or in batches) so that this can help you locate the offending one?

If none of the previous alternatives are possible, you can follow this process:

  • Create a table with the same structure as your target one, but without any PRIMARY KEY or UNIQUE constraints. Let's call it load_table

  • Import your data to this table. It should not complaint of PRIMARY KEY constraints because there isn't any.

  • Perform the following query to find out duplicates:

    SELECT 
    k1, k2, k3 ... kn 
FROM 
    load_table
GROUP BY
    k1, k2, k3 ... kn
HAVING
    count(*) > 1

    where k1, k2, k3 ... kn are all the columns that would comprise the primary key of your target table.

Using these techniques, you will find the duplicates that SQL Server finds but elude the methods you've used up-until-now.

Upvotes: 1

Related Questions