locked
migrate 2 databases into one database--copy data and all from 2 databases into 1 database RRS feed

  • Question

  • Hi All,

    Assume I have database1 and database2.. I need to combine these 2 databases into third database Database3 instead of using 2 databases...I have many like these to do....

    the tables might have same structure or might not in the databases.... sometimes might be in same server or different server...

    Can I know what would be the best method or different ways to insert the two databases into new third database...either any stored proc or ssis or dynamic sql.....

    Thanks in advance...

    Thanks,

    JR

     

    Tuesday, July 6, 2010 1:28 AM

Answers

  • I have solved using dynamic sql.

    Thanks All,

    JR

    • Marked as answer by JR1811 Monday, July 26, 2010 3:10 PM
    Thursday, July 22, 2010 9:00 PM

All replies

  • How many tables you're talking about? If the number of tables not too big, then I suggest to write the appropriate script manually.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Tuesday, July 6, 2010 3:28 AM
    Answerer
  • Hi Naom,

    I have many other databases to combine and also each database may have 100+ tables.

     

    Thanks,

    JR

    Tuesday, July 6, 2010 3:32 AM
  • JR

    Take a look at SQL Compare tool of www.red-gate.com

     


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Tuesday, July 6, 2010 5:56 AM
  • Hi,

    I have seen Sql compare but I need some script :(

    Thanks,

    JR

    Tuesday, July 6, 2010 1:22 PM
  • You could always generate script and include checkbox data for the two source databases. This will give you the script for inserting the data. From there search and replace so that it matches the new db design (target database), ie columns etc.

    You have to examine all foreign key relations and so forth when building the new target database. So a couple of database diagrams for the two old databases, and a for the new is a good starting point.

    As always you'll have to set it up in a lab environment, and validate that teh result is the desired by running queries against the old databases, and a modified querie against the new target database. Then compare the results.

    Qurious though, why not just create views in a third database that utilizes the two target databases. What goal is to be achieved?


    Regards Marten Rune Microsoft Certified IT Professional Database Administrator/Developer 2008
    Wednesday, July 7, 2010 10:22 AM
  • Hi

    There is no magic tool from MS that provides what you want. And what if the defenition of the table will be changed tomorrow , that means it should be added to the script.

    If you do not like SQL Compare , take a look into SSIS packages


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, July 7, 2010 10:37 AM
  • Hi,

    Thanks all for the reply.

    I can do this with stored proc but what if a database consists of more tables??

    I was just thinking if there might be some better ways to merge 2 databases into one....

     

    Thanks,

    JR

    Wednesday, July 7, 2010 2:50 PM
  • You want to merge 2 Databases. Ok.

    Are all tables same? Or Different?

    You want to INSERT data from both databases and merge them into 1?

     

    Wednesday, July 7, 2010 10:07 PM
  • Hi,

     

    Ya, the tables in them are same ..and some cases different....

    I want to merge them into new  database.

     

    Thanks,

    JR

    Thursday, July 8, 2010 2:05 PM
  • Hi,

     

    Ya, the tables in them are same ..and some cases different....

    I want to merge them into new  database.

     

    Thanks,

    JR

       And What about Data? You want to merge data too? will it be any duplicates?
    Thursday, July 8, 2010 5:41 PM
  • Hi,

     

    Ya even the data.... for some databases i should have duplicates and few without dups

    I tried writing dynamic sql  where it generates the scripts for writing stored procs individuallly for each table by using that script.. its working for common tables...

    but still i m wondering about the uncommon tables in database....how to log them into a temptable using dynamic sql... fr example..

    if 2 db A & B.. if there is a table in A and not in B.... or vice versa..i should have a log.. also..i should log the ones which have change in columns tooo...

     

    Any suggestions......

     

    Thanks,

    JR

    Thursday, July 8, 2010 8:30 PM
  • I think you need to be super specific what you describe. "Ya even the data.... for some databases i should have duplicates and few without dups" does it mean table or database? Duplicates in database (Table) or duplicates in table (Data)?

    give some example so that reply can be quicker and perfect. 

     

    before it was about doing the task. now it's for log. you want to merge them into new database? or you want log only?

    Monday, July 12, 2010 2:06 PM
  • I have solved using dynamic sql.

    Thanks All,

    JR

    • Marked as answer by JR1811 Monday, July 26, 2010 3:10 PM
    Thursday, July 22, 2010 9:00 PM