Primary Key Violations in DTS package - import from Excel file

Question

Primary Key Violations in DTS package - import from Excel file

jenniferhu46

SSCrazy

Points: 2990
More actions
July 25, 2005 at 9:16 am

#66739

Hi Experts,
I have a table in SQL Server 7.0 and I’m trying to import 2 columns (Update_Ind and Update_DateTime) data from Excel file and EMPLOYID is the primary key :

My table is like this:
CREATE TABLE upr00100
([EMPLOYID] [char] (15) NOT NULL ,
       [EMPLCLAS] [char] (15) NOT NULL ,
       [INACTIVE] [tinyint] NOT NULL,
       [Update_Ind] [tinyint] NULL ,
       [Update_DateTime] [smalldatetime])
I have created a simple DTS Package that picks up the excel file and is supposed to import the 2 columns data back into my table.

The source is Microsoft Excel 97-2000 new_table and the destination is my SQL Server UPR00100 table. The Transform Data Task is set for mapping 2 columns, Update_Ind and Update_DateTime. However, the package fails because the duplicates in Primary Key. Error:
The task reported failure on execution.
Violation of Primary Key constraint ‘PKUPR00100’. Cannot insert duplicate key in object ‘UPR00100’.

Can someone tell me that how DTS package manage the data import with the primary key look up and what should I do to import these 2 columns by looking up the primary key in the UPR00100 table.
Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246986 More actions · Answer 1

Is the primary key an identity field? If not, you need to populate it (uniquely) as part of the import - otherwise SQL Server tries to create multiple records with a NULL primary key, and that's never going to work

Or (he says, after reading the post again) do you also have the primary key on the spreadsheet and all you are trying to do is match on primary key and update the update_ind and update_datetime fields if there is a match?

Regards

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

jenniferhu46 SSCrazy Points: 2990 More actions · Answer 2

The primary key is not an identity field. And if I add this primary key into my import, I got the same error:

Violation of Primary Key constraint ‘PKUPR00100’. Cannot insert duplicate key in object ‘UPR00100’.

Yes, I have the primary key on the spreadsheet and I need to update the update_ind and update_datetime fields if there is a match on the primary key.

What should I do? Can you explain in more details. Thanks.

Phil Parkin SSC Guru Points: 246986 More actions · Answer 3

It may be possible to do all of this in one step through DTS, but my DTS is not good enough to know. What I would do is

a) Create a new 'import' table, containing EmployID (PK), Update_Ind and Update_Datetime fields.

b) Perform the import to this table, including EmployID (should work fine - there's no data).

c) Execute a SQL Server query to UPDATE the records with a match on PK (existing PK records):

update u

set update_ind = i.update_ind,

update_datetime = i.update_datetime

from UPR00100 u inner join IMPORT i on u.EmployID = i.EmployID

d) Execute a SQL Server query to INSERT the records which do not have a match (these are new EmployID records)

insert into UPR00100(EmployID, update_ind, update_datetime)

select i.EmployID, i.update_ind, i.update_datetime from import i

where i.EmployID not in (select u.EmployID from UPR00100 u)

Hope that makes some sense - good luck.

Phil

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

jenniferhu46 SSCrazy Points: 2990 More actions · Answer 4

Thank you Phil, that's it, the problem is resolved.