Post reply

Merging of Different databases into one

  • October 1, 2008 at 6:10 am

    #154774

    Hi All,

    I have four different databases having common structure. i.e tablename and column name. These databases are used for different products. i.e for Product1, the databasename is ProdData1...

    I want to create one database in which i want to have the data from all the 4 databases. In this database i have created additional column (Product) in all the tables.

    Now i want to import all the tables from all the databases with the identification of the Product in this database. Also new record and updations should also be updated in this database as soon as it is updated in any of the four databses.

    Any suggestions will be highly appreciated. I am not sure whether this can be done through replication.

    Thanks in Advance.

  • October 1, 2008 at 6:23 am

    #879420

    I would suggest using a UNION clause to import all four tables in the first instance. Then use triggers to replicate updates or additions.

  • October 1, 2008 at 6:26 am

    #879423

    try this one,

    INSER INTO TABLE

    SELECT * FROM

    UNION

    SELECT * FROM

    UNION

    SELECT * FROM

    UNION

    SELECT * FROM ;

  • October 1, 2008 at 6:28 am

    #879425

    If you need to have the data kept up to date from the four servers then you should be able to do this with transactional replication.

    You can set up one way transactional replication from each of the four servers replicating the data to the common server. You may have issue if you use auto-Id columns and will have to factor in an ID range into all of your publishers in order to avoid conflicts in the data.

  • October 1, 2008 at 7:46 am

    #879473

    What is the best way here? Triggers or transactional replication?

    I know how to create the triggers? but is it a suggested procedure? i feel that if the records are inserted from 4 tables by the trigger into one table then it may create locking issues..

    I am not sure about transactional replication. Can you give me the detail procedure to implement it through transactional replication. Please note that the structure of Central Database is different from the 4 databases. i.e central database is having additional column for the Product.

    Thanks to all of you for the replies.

  • October 1, 2008 at 8:27 am

    #879502

    shakti (10/1/2008)

    I want to create one database in which i want to have the data from all the 4 databases. In this database i have created additional column (Product) in all the tables.

    I see your data modeling skills are getting better, way to go! 😀

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  • October 1, 2008 at 8:42 am

    #879516

    shakti (10/1/2008)

    What is the best way here? Triggers or transactional replication?

    I know how to create the triggers? but is it a suggested procedure? i feel that if the records are inserted from 4 tables by the trigger into one table then it may create locking issues..

    I am not sure about transactional replication. Can you give me the detail procedure to implement it through transactional replication. Please note that the structure of Central Database is different from the 4 databases. i.e central database is having additional column for the Product.

    Thanks to all of you for the replies.

    i would go for transactional replication over triggers, as triggers will not be smart enough to handle conflicts in the way replication can.

    There is a lot of information on BOL about replication strategies and MSDN also has a lot of information.

    The sort of ssytem structure you are trying to acheive is outlined in this MSDN page

    http://msdn.microsoft.com/en-us/library/ms151790(SQL.90).aspx

  • October 1, 2008 at 12:39 pm

    #879687

    steveb (10/1/2008)

    shakti (10/1/2008)

    What is the best way here? Triggers or transactional replication?

    I know how to create the triggers? but is it a suggested procedure? i feel that if the records are inserted from 4 tables by the trigger into one table then it may create locking issues..

    I am not sure about transactional replication. Can you give me the detail procedure to implement it through transactional replication. Please note that the structure of Central Database is different from the 4 databases. i.e central database is having additional column for the Product.

    Thanks to all of you for the replies.

    i would go for transactional replication over triggers, as triggers will not be smart enough to handle conflicts in the way replication can.

    There is a lot of information on BOL about replication strategies and MSDN also has a lot of information.

    The sort of ssytem structure you are trying to acheive is outlined in this MSDN page

    http://msdn.microsoft.com/en-us/library/ms151790(SQL.90).aspx

    Wow!.. this was an excelent answer... for a different question 😀

    Poster wants to migrate from 4 DBs to 1 DB, just once! 😉

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  • October 1, 2008 at 12:48 pm

    #879692

    Poster wants to migrate from 4 DBs to 1 DB, just once! 😉

    Really? I thought this part of the OP implied that the data would need to be kept updated.

    Also new record and updations should also be updated in this database as soon as it is updated in any of the four databses.

  • October 1, 2008 at 1:59 pm

    #879725

    Yes, you're right. He says:

    Also new record and updations should also be updated in this database as soon as it is updated in any of the four databses.

  • October 1, 2008 at 3:46 pm

    #879760

    shakti (10/1/2008)

    updations

    now there's a word for you

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

Viewing 11 posts - 1 through 10 (of 10 total)

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