Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
simflex
Constraint Violating Yak Guru
327 Posts |
 Posted - 2004-05-13 : 11:31:59
|
| I successfully exported a database with one table from Access database into SQL Server database.Now, I am trying to use dts to setup a process whereby any new records added to access db will be appended to the sql server db on a daily basis.DTS job is failing because of the following error: Error: -2147467259 (80004005); Provider Error: 2627 (A43) Error string: Violation of PRIMARY KEY constraint 'PK_spills_geocd1'. Cannot insert duplicate key in object 'spills_geocd1'. Error source: Microsoft OLE DB Provider for SQL Server I thought that if I use dts to setup data transfer and use the "append to destination table" option, I would only be appending data, not fieldname.Can someone help on this, please? |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2004-05-13 : 12:00:16
|
| You are trying to insert a duplicate into your primary key... It doesn't matter if you are just appending data, it still has to be unique to the key you have used on the table... |
 |
|
|
simflex
Constraint Violating Yak Guru
327 Posts |
Posted - 2004-05-13 : 12:03:40
|
| Thanks for the response.Ok, you have discovered the problem.Can you think of a workaround to this problem?Do I need to make a non-unique key or remove it or what? |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2004-05-13 : 12:15:15
|
| Try putting the results into a work table and only insert those which are different..You don't want to remove the key else you may have trouble with your data at a later date... |
|
|
|
simflex
Constraint Violating Yak Guru
327 Posts |
Posted - 2004-05-13 : 12:40:35
|
| Again, Rick, thanks for your help but...Try putting the results into a work table and only insert those which are different..I have no idea what you mean with the statement above.Can you please clarify? |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2004-05-14 : 04:10:35
|
| Pull the results into a table that you truncate first and that has no primary key, then join it to the real table and only do an insert where the records are different... Or delete those that have the same key out of the real table first, then insert the new records... |
|
|
|
|
|
|
Automating export from access db