Sql Server Primary Key Violations

I am trying to upsize an MS Access Database to SQL 2000 and running into problems with som of my append and update queries.

In MS access if you run an append query and some of the data your are appending violates the primary key of the table you are appending to, Access will disregard the duplicate records, but will go ahead and append all the rest of the records that don't violate the primary key.

If you run the same query in SQL Server, no records get appended if only one record violates the primary key (I think). Is there a way to make SQL server act the same as MS Access - Disregard the records that violate the primary key, but go ahead and append the records that don't violate the primary key?

No, and no one would want it to.

You should get rid (remove/delete...whatever) all of the duplicate records that violate the primary key rule first.

Thanks for your reply Memnoch



There just has to be a way to do this. This is what I’m trying to do.



This is strictly a reports database, kind of a warehouse set up that is currently in MS Access that I’m trying to upgrade to SQL Server. Except for a few lookup tables, all the tables will be truncated and then filled again with current data weekly.





I have text files that are downloaded which I copy into Access tables to use for reports. Every time I run the updates I completely empty out the tables and re-import current data. In Access I did set some primary keys on the table to prevent an excessive amount of duplicate data. It worked it worked because access would ignore the data that violated restraints and copy everything else to the tables. I have no control over the text files that are downloaded from a DB2 database so I can’t control what I get. In Access I could at least link the text files and review the data, but in SQL server you can’t link the table to look at the data.



I set up a dts package in SQL server to copy the data to my tables and that’s where I’m running into problems with duplicate keys. The package fails because of the duplicates in the primary keys or some other violation. I thought about trying to do bulk inserts but when I tried that the only files I could browse for were on my c or d drive. I couldn’t figure out how to browse one on the network drives where the text files are downloaded to.



I’m new to SQL server to please excuse my ignorance!. Any other comments or help would be greatly appreciated.

What about adding another column to the table, set it as a uniqueidentifier data type with IsRowGUID to "Yes". This column would simply exist to make every row unique.
Then set this column as the primary key and then you won't have any problems with PK violation when running your dts package to insert the text file data.