How to I combine two exact same table structures different data?

p.s. In my situation, the Employee table was all I was concerned about there old key values being overwritten. Evaluations had an IDENTITY field as it's primary key but since there were no FK relationships from it to any other tables, it's value was used only to provide uniquness and no other tables were aware of its value. Your situation may be different, in which case, you would need to replicate the process of creating a temp field, populating it, merging tables, updating foreign keys, then deleting the temp column.

I would use DTS to do this, read this:

http://www.databasejournal.com/features/mssql/article.php/3086891

Using DTS does not handle the scenario of combining primary keys and being able to update the relationship between primary and foreign keys.

I thank you for pushing me in the right direction.  I have many relationships and 12 tables I will have to be updating (not exactly large).  I am getting a good plan in place right now. I also thank you for noting how hard this will be because my boss will read this and might get off my back for a little while.

Thanks much.

Thanks for the points and grade. Like I said, mine was only one table (with primary keys). We had two separate datbases on two different servers that had an HR system for two different physical locations (hospitals). There were quite a few tables with foreign-key relationships back to the primary employee table (evaluations, licenses, certifications, job positions, notes, even parking permits and several others).

Along with combining two separate databases into one, a lot of normalization was done (the original employee table had columns for home phone, work phone, cell phone, pager and fax, plus with the conversion they wanted to add personal cell and emergency contact number) and some other situations. I ended up spending close to 40 hours developing a conversion script that ended up merging all the data, loading the normalized tables, etc.

Good luck!