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
** There are 13 rows of data **
SQLServer (NTSERVER\QC Issues\kcj_ControlDocTable):
- CDCFNo - Varchar(10) - key field
** 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