Append data to table with PrimaryKeys?

[jduehmig]

I'm hoping someone can help me out with this. I think the answer is pretty straightfoward but I would rather ask first rather than end up corrupting live data.

I used the SQL Server Migration Assistant for Access to upsize an Access 2003 database to a SQL 2005 backend. The migration appeared to work OK, and I was able to put the new system in production. About a week later I was informed that about 5000 historical records were missing. After digging into things I found that three records with illegal characters caused the import of the remaining data in one table to not complete. I have now removed the offending records and imported the entire contents of the table in question to a temporary table in SQL. Now I need to append the missing data into the correct table without affeting the good records and the new records that have been entered since the system went on-line. My concern is the primary keys. The PK field is auto-incrementing, and when I tried to do a simple import I received the primary key violation error. Can anyone tell me how to import the non-duplicate records while maintaining PK integrety?
Thanks in advance,
Joe

[zmbd]

Question: Related tables?

For example:
Prior to upgrade:
tblParent
[Parentpk][other fields....]
[ 1][other fields....]
[ 2][other fields....]
[ 3][other fields....]
[ 4][other fields....]
[ 5][other fields....]
[ 6][other fields....]

tblchild
[childpk][FK_to_Parentpk][other fields....]
[ 101][ 1][other fields....]
[ 102][ 2][other fields....]
[ 103][ 3][other fields....]
[ 104][ 4][other fields....]
[ 105][ 5][other fields....]
[ 106][ 6][other fields....]


So say on upgrade [Parentpk]=3 caused the stall so that the records for [Parentpk]>=3 failed to import; thus the child records with [childpk]>=103 now have issues, either these didn't import (best case), or they now have invalid references back to the parent with new records in the parent having replaced the older records

after upgrade - worst case:
tblParent
[Parentpk][other fields....]
[ 1][other fields....]
[ 2][other fields....]
[ NEW3][other fields....]
[ NEW4][other fields....]
[ NEW5][other fields....]
[ NEW6][other fields....]

tblchild
[childpk][FK_to_Parentpk][other fields....]
[ 101][ 1][other fields....]
[ 102][ 2][other fields....]
[ 103][ OLD3][other fields....]
[ 104][ OLD4][other fields....]
[ 105][ OLD5][other fields....]
[ 106][ OLD6][other fields....]
YIKES!

You could change the field for the PK from auto to numeric(long) and the run an update query to alter the table(s) and soforth; however, that can be tedious for more than one table.


Now, personally, what I would do is take my original data, fix the offending records and upgrade to a new backend file. Double check the record counts.
Take the production backend offline... keep the users out while you update stuff.
Now append the new records from the current production backend to the new backend.
Drop the production backend in the trash and bring the new backend online as current production.