Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Violation of Primary Key constraint...

Posted on 2002-05-21
Medium Priority
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
** There are 13 rows of data **

SQLServer (NTSERVER\QC Issues\kcj_ControlDocTable):
- CDCFNo - Varchar(10) - key field
- Date
- Shift
** 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
Question by:Naomh
  • 2

Expert Comment

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.

Accepted Solution

spcmnspff earned 1200 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.

Expert Comment

ID: 7052283
How did this turn out?

Author Comment

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!


Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

579 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