Primary Key Violations in DTS package - import from Excel file
-
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
-
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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
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.
-
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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Thank you Phil, that's it, the problem is resolved.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply