Post reply

Merging Databases

  • July 11, 2007 at 3:12 am

    #372767

    Guys hi,

    i need your help on this please.

    I have two databases (same collation), that i want to merger together.

    One database is a datawarehouse of our erp system, and the other is the datawarehouse of our in-house developed application. Some of the tables in the two databases have the same names however i am not sure if the tables are really needed or are just left from some previous migration, and are junk.

     

    What i have done is that I backed up and restored the ERP Datawarehouse (DW_3) in a new database called DW_3_migration_test. Then copied all tables of the in house datawarehouse database (name: DW3_test) to the DW_3_migration_test. So now the DW_3_migration_test contains all tables.

    I run the DTS packeges changing the database names to the new one and all run ok. However i am not sure if any tables shouldn't be overwritten or anything like it.

    Is what i did a correct procedure?

    How should i do it? your advise please would be valuable...

     

    "If you want to get to the top, prepare to kiss alot of bottom"

  • July 11, 2007 at 3:22 am

    #718540

    If those that that exist in both the database have the same bnumber of column and the same columns then you can merge the data instead of having it in different tables right.

    Cheers,
    Sugeshkumar Rajendran
    SQL Server MVP
    http://sugeshkr.blogspot.com

  • July 11, 2007 at 3:41 am

    #718546

    Sugesh, thank you for your answer.

    Please be more a bit more specific (i am a business analyst, not a dba! ). Firstly, yes all tables of both databases have the same number of columns (i am talking about the tables that are common).

    However, let me give you an example. in the ERP DW (datawarehouse db) i see a table called Dim_Examcentres. This table exist with the same name in the "inhouse application" DW. However in ERP DW, the table has 2058 rows while in the "Inhouse DW" the same named table has 2133 rows.

    In the above case for example should'nt i check whether joinng the two tables have the same records on the same primary keys? For instance what if the table in the ERP DW has on primary key 1 of the table Examination_centre the examination centre named "SomehowA" and the same table on the inhouse DW on that primary key has another examination centre? (named "somehowB"). Isn't there a problem when i copy the second table to the database that contains the first table?

    also, is there anything else that comes in your mind that i should check?

     

    Thank you very very much for your time in answering my silly question!

     

     

     

    "If you want to get to the top, prepare to kiss alot of bottom"

  • July 11, 2007 at 4:29 am

    #718556

    First start with comparing the data between the two databases. You can use tools like SQLDelta, Quest Data Compare or Red Gate Data Compare. Then decide on which data to keep or delte.

    Cheers,
    Sugeshkumar Rajendran
    SQL Server MVP
    http://sugeshkr.blogspot.com

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply