asked on

Violation of Primary Key constraint...

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

avbsql

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.

ASKER CERTIFIED SOLUTION

spcmnspff

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

spcmnspff

How did this turn out?

Naomh

ASKER

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