Post reply

How to Merge 3 database to 1 database

  • June 19, 2010 at 10:59 am

    #223989

    Hello,

    I've an application that store all the information in a SQL Database local because the internet connections are sometimes bad. We do this on three locations. Now we want to merge all the data from the three locations to one database so that we can put all the information on the internet.

    What is the best way to do this. Also with the ID's and order numbers.

    Hope somebody can tell me more.

    Regards,

    Bart Jan

  • June 19, 2010 at 1:56 pm

    #1182460

    there's not a simple script that does it; everything depends on the specifics of the data in question...foreign keys, primary key definitions, and more. you'll need to analyze the data and the relationship between the tables;

    a common technique is to have identity() properties for each location have different seeds so they can be merged easily, so for example location 1 starts at 1, location 2 starts at 100,000,location 3 at 300,000 and so on; foreign keyed tables to that master table can be treated similarly, but it's not critical on the child data , as long as the FK.

    since you'll be doing this regularly, you'd want to use SSIS to do the merging and save the solution for future use.

    another option is to consider merge replication, with each of the "remote" db's publishing to the main db;

    for any specific answers, we'd really need specific info...create table definitions, guesstimate on the number of records to merge, a description of the business process sometimes helps.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • June 19, 2010 at 2:11 pm

    #1182463

    Hello Lowell,

    Thanks for your comment.

    I think our solution will be get a interval of 1.000.000 so that the three locations can add enough data. Normally the people will do 30 transactions a day for each location.

    All the data should be merge/sync two or three times a day.

    Hope you can give me some more information with the new information from above.

    Thanks,

    Bart

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

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