Merge two seperate databases. 1

[HestonJames]

Good afternoon All,

I'm hoping this'll be a possible task, if not a little tricky.

On the back end of our application we have two databases, one contains general data on users, customers, products ect and the other contains a large amount of statistical data for reporting and suchlike.

Now, the problem here lies in as much that we want to start cross referencing the databases, so I want to start logging report data which is in reference to a user or customer.

To be able to do this properly I think we need to merge the two databases into one unified database, is that right?

At present the two databases have no identicaly named properties or objects, they are completely different.

Is is possible to merge these to databases together? so that all the tables sit in one location? I can then contruct new columns and FK references ready for the new customer related reporting.

Is this possible? is it a simple task?

Long way around I could always reconstruct the tables from one database in the other and migrate the data accross, I thought I would come and get your experience on the job at hand.

Thanks all,

Heston

 

[JayKusch]

First off YES ... you could use the export feature to export all of the data and DB schema from one DB to the other.

The backend to this "export" process is SSIS (SQL Server Integration Services) if you are using SQL Server 2005 and DTS (Data Transformation Services) if you are using SQL Server 2000.

My next question would be "why" do you want to bring the two DB together?

I am betting you can accomplish the "... logging report data which is in reference to a user or customer." with the databases just as they are unless there is a major performance hit when using cross daa

Thanks

J. Kusch

 

[JayKusch]

First off YES ... you could use the export feature to export all of the data and DB schema from one DB to the other.

The backend to this "export" process is SSIS (SQL Server Integration Services) if you are using SQL Server 2005 and DTS (Data Transformation Services) if you are using SQL Server 2000.

My next question would be "why" do you want to bring the two DB together?

I am betting you can accomplish the "... logging report data which is in reference to a user or customer." with the databases just as they are unless there is a major performance hit when using cross database calls.

Drop us a little more info as to what "reporting" you are looking for and maybe we can get you to a point where you do not need to merge the DBs.

Thanks

J. Kusch

 

[HestonJames]

Jay,

Thanks for the quick reply mate, very kind of you.

I'm using 2k5 so something like SISS might be the route to go if I DO opt to merge the two, however, you mentioning that I dont have to has me interested.

I can cross query between the two databases? Is that correct?

Lets take a short example, if I want to do a basic INNER JOIN query to pull data from the two databases, how could I do this? what does the syntax look like?

We'll have to test this out and see how performance works for us but to be honest I'm just looking for a simple solution. We have a remodel of the application in the works at the moment and this is just to tide us over for a few months untill its complete.

In the new designed model they're all in one database so it'll be a non-issue.

Thanks,

Heston

 

[JayKusch]

Well there is going to have to be some columns that tie the client data in the one DB to the stats data in the other no matter if the data is in one DB (like your current intentions are) or whether the data is in 2 DB like the current environment.

So in saying this let's say for example sake we have some type of ID key on the client info in the one DB and we have a client ID that is on the stats DB info.

The code would look something like:

SELECT UserName, UserAddress, UserWhatEver,
       Stats1, Stats2, Stats3
FROM   UserDBName.dbo.UserInfoTable  AS uit
JOIN   StatsDBNmae.dbo.StatsTable1   AS st1
ON     uit.UserID = st1.UserID

Thanks

J. Kusch

 

[HestonJames]

Hello J,

Ok I See! Hmm, Interesting. For the moment I think that cross DB this query approach might best suit us as it saves any major changes to the database, just a few here and there.

Presumably we can still define FK references accross the databases aswell? Using a similar syntax of the dbname.dbo.tablename manor?

Thanks J, I appreciate this advice, you might have just saved me some time and a headache or two :-D

Heston