Violation of PRIMARY KEY constraint. Cannot duplicate key in object

0
votes

In my DTSpackage, I have this to create a table...

CREATE TABLE [fwAIR].[dbo].[tblPRODUCTIVITY] (
    [CMS_Login] varchar (5) NOT NULL, 
    [DeptIndex] tinyint NOT NULL, 
    [CMS_Date] datetime NOT NULL, 
    [AssociateNumber] int NULL, 
    [TotalCalls] smallint NULL, 
    [StaffTime] int NULL, 
    [ACDTime] int NULL, 
    [ACWTime] int NULL, 
    [PureAuxTime] int NULL, 
    [AuxInTime] int NULL, 
    [AuxOutTime] int NULL, 
    [HoldTime] int NULL, 
    [RingTime] int NULL, 
    [AvailableTime] int NULL, 
    [CallTime] int NULL, 
    [TotalNPTMinutes] smallint NULL, 
    [Active] char (1) NULL, 
    [iOther] int NULL, 
    [RcdCreateUser] varchar (15) NULL, 
    [RcdUpdtUser] varchar (15) NULL, 
    [RcdCreateDateTime] datetime NULL, 
    [RcdUpdtDateTime] datetime NULL,
    sdtImportDate smalldatetime NOT NULL DEFAULT GETDATE(),
    CONSTRAINT idxPRODUCTIVITY_PK PRIMARY KEY NONCLUSTERED (DeptIndex, CMS_Login, CMS_Date)
);

CREATE NONCLUSTERED INDEX idxPRODUCTIVITY
ON tblPRODUCTIVITY (AssociateNumber, CMS_Date);

CREATE CLUSTERED INDEX idxPRODUCTIVITY_DeptIndex_Date
ON tblPRODUCTIVITY (DeptIndex, CMS_Date);

When I import a text file in to this table, I get the following error.

Error Source: Microsoft OLE DB Provider for SQL Server

Error Description: The take reported failure on execution. The Statement has been terminated. Violation of PRIMARY KEY constraint "idxProductivity_PK". Cannot insert duplicates key in object 'tblProductivity'.

I read online I can add to the create table portion

ALTER INDEX idxPRODUCTIVITY_PK on fwAIR.dbo.tblPRODUCTIVITY
SET (IGNORE_DUP_KEY = ON);

But was told it wouldn't work and it would still error out. I was told I can also "create mimic IX and added that option". Which should work.

I'm kinda lost from this point on getting rid of that error and having it continue on with the import.

4
sqlsql-server
I don't know what you want to have happen. If you have duplicate data, then you have duplicate data, and you cannot get it into your database. – John Saunders
I know for certain that its the text file with the dups. I just want to ignore the dups and continue on. I think the import as soon as it see a dup, stops! I don't want that. I just want it to continue on. – user3191496
You cannot have it ignore the dups! You must import first to a staging table and then clean your data. Oot of curiosity why are you using a DTS package? If this is a SQLServer2005 or higher db, then you should be using SSIS packages as DTS has long since been discontinued. – HLGEM

4 Answers

2
votes

I would suggest that you create a staging table where all the columns are varchar() (or nvarchar()).

Import the data into the staging table. Then try to create the actual table from that one.

If you have a problem in the first step, then the data file is probably not in the format you think it is in. If you have a problem in the second step, you can refer back to the actual data to see what the issue is.

2
votes

Import your data into a staging table, keep the datatype as lose as possible, once you have data in sql server.

eliminate any duplicates and and more data manipulation if needed and then insert data into your actual target data.

It is never a good idea to import data directly into your tables. as it can leave you with a lot of cleaning to do if there is any garbage data in your source file. :)

1
votes

Its not an error. Your table definition says that you are not allowed to have 2 (or more) rows with the same values in the DeptIndex, CMS_Login and CMS_Date columns. This is the primary key (or unique identifier) for each row in your table..

so either:

  1. If it should be allowed, then you remove or amend the primary key constraint so that it contains a combination of columns which SHOULD be unique (Altering it like you've tried won't work for a primary key) or
  2. You remove the duplicates from your input data manually and then re-try
1
votes

If you're getting a primary key violation it is because 1) there are duplicate rows in the file you are trying to import or 2) if your table has data, the text file you are trying to import has one of the same rows that's in the table.

What you have to do is 1) remove the primary key or 2) change the columns that make up the key so that the rows are truly unique.

To find out what you rows are duplicate, you could remove:

CONSTRAINT idxPRODUCTIVITY_PK PRIMARY KEY NONCLUSTERED (DeptIndex, CMS_Login, CMS_Date));

Import the text file and then run this SELECT statement:

SELECT DeptIndex, CMS_Login, CMS_Date 
FROM [fwAIR].[dbo].[tblPRODUCTIVITY] 
GROUP BY DeptIndex, CMS_Login, CMS_Date 
HAVING COUNT(*) > 1