By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,678 Members | 2,439 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,678 IT Pros & Developers. It's quick & easy.

Append data to table with PrimaryKeys?

P: 1
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
Dec 19 '13 #1
Share this Question
Share on Google+
1 Reply


zmbd
Expert Mod 5K+
P: 5,287
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.
Dec 19 '13 #2

Post your reply

Sign in to post your reply or Sign up for a free account.