Results 1 to 13 of 13
  1. #1
    Join Date
    Dec 2004
    Posts
    9

    Question Unanswered: Combining 2 SQL databases

    Is there an easy way to combine to SQL databases? Both DBs have the same structure but different data. If there just so happens to be duplicate records what will happen? Does anyone have any idea of where I should start at?

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6

    many options

    There are a number of ways to skin this cat and there are a number of issues to look out for. Don't use the copy database wizard, it's a real peice of crap and it requires all users to be logged out and all connections to be cut.

    Personally I would go into the SQL EM, right click on your database, go to Generate SQL Script and I would script out the database and all objects. Then I would open up the generated file in the QA and change the database name and execute the script.

    This is where things get a little sticky. You might have problems with duplicate primary keys because of your 2 sets of tables and any foriegn keys that these primararies populate. Your going to have to figure this one out and without knowing your DB I am going to leave this one to you. Other things you might want to disable or look out for before you move the data are constraints and triggers (especially triggers that insert or update other records).

    Once you have all of these little things compensated for, I would then DTS all of the data over from the original 2 DBs.

    As for dupes, welcome to the world of the DBA. Your just going to have to clean these up using queries. Regular part of the job.

  3. #3
    Join Date
    Dec 2004
    Posts
    9
    OK, I'm really new to MS SQL server.....

    Whats is SQL EM? And where is the QA? Is there a client SQL version I can use on my workstation to manager all this without having to work on my server?

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    If he's lucky, the databases were designed with GUIDs rather than IDENTITY keys....

    Couldn't resist!
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    SQL EM is the SQL Enterprise Manager. The QA is the query analyzer. Both of these and Books Online can be installed on your desktop using the installation disks. If you are this new, I would'nt attempt anything as complicated as this until you do a little reading. Buy a book. Take a class. Do something.

    But if you insist on moving forward make backups of everything before you start thrashing around.

    On your server, and maybe on your desktop you can find these tools by going to Start \ Programs \ Microsoft SQL Server.

  6. #6
    Join Date
    Dec 2004
    Posts
    9
    These databases were converted from MS Access. They were originally designed by a company called Tigerpaw Software. They have no support for combining Databases though.

  7. #7
    Join Date
    Dec 2004
    Posts
    9
    I'm not new to databases...I used mySQL and phpMyAdmin to manage it. I'm a programmer that got thrown into being a DBA....aint life grand.....

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by tready
    I'm not new to databases...I used mySQL and phpMyAdmin to manage it. I'm a programmer that got thrown into being a DBA....aint life grand.....

    Welcome to Olympus....

    Give a sample fo 2 tables that are identical in both databases.

    Script them using Enterprise Manager, and post the DDL

    Did you install the sql server client side tools?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Dec 2004
    Posts
    9

    Question

    I just jumped behind my SQL server ad generated a script off of one of the DBs. It of course created a .sql file which I can use to re-create the tables. So I need to do that for both. How do I get them in the same db...just change the name of the DB it creates and then run both scripts?

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I would size the database first and create the databse via Enterprise Manager.

    I would then run the scripts in Query Analyzer.

    But are the objects the same or not?

    If they are you'll have trouble.

    Also, there is really no need to combine them

    You can query each from it's own database using their database names

    USE Northwind
    GO

    SELECT * FROM Employees

    SELECT * FROM pubs.dbo.Employee
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    Dec 2004
    Posts
    9
    The objects are all the same structure, but the data if different for the most part. Some customers are in both databases because we employ a lot of jackasses...(excuse me)....nice people...who don't follow directions. There are two many dups to go in a manually delete all of them. Lucky for us the software we use has a utility to combine two records.

    So I take the scripts I have and run them in the query analyzer...

    And what do you mean there is no need to combine them?
    Last edited by tready; 12-13-04 at 14:55.

  12. #12
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Did you see the select with the three part name?

    But then I guess the application needs to look at only 1 database...right?

    How many tables are we talking about?

    I would dump and restore 1 database, then use DTS to append the data to their "cousin" tables....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  13. #13
    Join Date
    Dec 2004
    Posts
    9
    There are 50+ tables......

    What do you mean by dump and restore with cousins?

    And yes the app has to only see one database....

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •