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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql help 8 55
SQL Server 2012 r2 - Query results have 2 seperate results instead of 1 2 21
SQL Server 2012 r2 - Varible Table 3 19
SQL view 2 25
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 …
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

816 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now