Solved

Violation of Primary Key constraint...

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

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…
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.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

856 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