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

Posted on 2007-09-28
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
    LVL 15

    Accepted Solution

    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

    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.
    LVL 8

    Expert Comment

    LVL 15

    Expert Comment

    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

    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

    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

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
    Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
    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.

    734 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

    Need Help in Real-Time?

    Connect with top rated Experts

    26 Experts available now in Live!

    Get 1:1 Help Now