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? :confused: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.|||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? :confused:|||If he's lucky, the databases were designed with GUIDs rather than IDENTITY keys... :)

Couldn't resist! :cool:|||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.|||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.|||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....|||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?|||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?|||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|||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?|||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...|||There are 50+ tables.....

What do you mean by dump and restore with cousins?

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