Solved

Violation of Primary Key constraint...

Posted on 2002-05-21
4
924 Views
Last Modified: 2009-12-16
Here's a major question worth loads of points.

I am trying to update a SQLServer (version 7) table with the data from an Access (97) table.  I'm using the 'Data Transformation Services/Import Data' wizard (so I can create a SQLServer package that can be scheduled to run every night) and I've created a DSN (CDCFUpdate) to get the Access table data.

The fields in both tables are the same, but the key-fields types are different.  For instance:

Access (nmtbl.mdb\CDCF Design Changes):
- CDCFNo - Autonumber - key field
- Date
- Shift
-etc.
** There are 13 rows of data **

SQLServer (NTSERVER\QC Issues\kcj_ControlDocTable):
- CDCFNo - Varchar(10) - key field
- Date
- Shift
-etc.
** There are 10 rows of data **  (11, 12 and 13 need to be captured from the Access table)

I'm getting the error: "Violation of Primary Key constraint: 'PK_kcj_ControlDocTable'.  Cannot insert duplicate key in object 'kcj_ControlDocTable'."

Q:  Do you feel that the error is occurring because I'm trying to update the SQLServer field data with identical field data from the Access table (in other words; the SQLServer key field is preventing us from entering repeat data)?  If so; I would like to create an update that updates the SQL Server table (kcj_ControlDocTable) with the data from the Access table (CDCF Design Changes) with the condition: "where CDCF Design Changes.CDCFNo <> kcj_ControlDocTable.CDCFNo" to prevent my update from trying to renenter data that is already there but I'm having trouble getting the Access table to show up in the wizard.

Q:  Or do you feel the error is occurring because the key-fields have different data types?

Q:  Or is it something else?

Thanks for your help
Naomh
0
Comment
Question by:Naomh
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 3

Expert Comment

by:avbsql
ID: 7024909
Basically its an auto generate in access and you need to make the same field in SQL as identity property and have
SET IDENTITY_INSERT ON in the script at the very start and SET IDENTITY_INSERT_OFF at the end. Hope this helps. Or it might be those numbers in key filed already exists and thats why it says violation of PK.
0
 
LVL 5

Accepted Solution

by:
spcmnspff earned 300 total points
ID: 7024938
You are getting the error because you trying to insert duplicate data. Using the DTS Wizard to append the data to the table will cause this primary key violation because each time the package runs, it tries to insert all of the data from the Access table to the existing SQL table.  The only way around this in the wizard is to check the 'replace existing data' radio button rather than 'Append data'.  This will push the entire contents of the access table to the SQL table each time the package runs.  

For a little more sophisticated solution you could design a package manually that imports the entire Access table into a temporay staging table in the SQL Database, does an insert into the SQL table based on an outer join to the SQL table itself on the key value, and then truncates the temp table.  But honestly this wouldn't be much more efficient.
0
 
LVL 5

Expert Comment

by:spcmnspff
ID: 7052283
How did this turn out?
0
 

Author Comment

by:Naomh
ID: 7053626
You were absolutely correct, the error was occurring because I was trying to "re-update" fields from the Access database to the SQLServer database that had previously been passed over (inserting duplicate data).  I brought this to the attention of the client and they explain that once the record had been passed to SQLServer it was ok to delete it from the Access database.  As you can see this clears up the whole issue.  Your suggestion to create a temp table and process from there is definately the correct step to take if I needed to retain the passed records in the Access database, but since the rules changed I didn't need to use a temp table.  In the end, everything is running great.  I truly appreciate your expertise and humble kindness!

Thanks,
Naomh
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

724 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question