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

Posted on 2007-09-28
Medium Priority
Last Modified: 2008-01-09
I have two different msde databases that I want to combine into one and on a couple of tables different data has the same primary key assigned when the data was entered.
Question by:UHCDIT
  • 4
LVL 15

Accepted Solution

dbbishop earned 1000 total points
ID: 19980562
I did this about a year ago and it was a nightmare!
You need to create a temporary column in your tables that have primary keys that are candidates for duplication. Then, update that column with the current primary key value.
Now, you will insert the contents of the data in the table in the second database into the first, which will generate new PK values. Now, you need to do an update of all your tables that still have to old PK from table two with the new value.
Assuming your main table is Employee and your table with FK relationships bak to Employee was Evaluations (this is a real-world example from what I did). It would look something like this:

update  Evaluations
set     EmpID = Employee.EmpID
from    Employee
inner join
        Evaluations  on Employee.OldEmpID = Evaluations.EmpID

After you've done all your updates you can delete the temporary column from your Employee table.
LVL 15

Expert Comment

ID: 19980577
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.

Expert Comment

ID: 19981168

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

LVL 15

Expert Comment

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

Author Comment

ID: 19982394
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.
LVL 15

Expert Comment

ID: 19982872
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!

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question