Violation of Primary Key constraint...

Posted on 2002-05-21
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
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

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 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.

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

756 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