linking databases question

Hello

Does anyone know the best way to get around this problem.

Basically, I have three servers containing the same database structure. The data is different, each pertaining to the location in question.

Speaking in laymans terms, I would like to throw all the data into a big box from the three servers so that I can get the information I need from ONE source.

A problem I foresee is primary keys. For example, let's take the patient table(I work in health) the patient_key field in table patient although unique within it's own database, is not unique across the other databases from the other sites I want to "throw into the box". I can't just add another primary key of my own because it would not appear in the other tables that patient_key does. Obviously patient_key is one of many such primary keys that share this problem.

Does anyone have any suggestions as to how to go about this problem. Is there a way to "join" these databases together, like a union command does to more than one query.

My organisation uses MS SQL Server 2000, we have no analysis or reporting services add ons.

My SQL server knowledge is ok when it comes to querying and simple stuff, but the more complex administration is all new to me. So treat me like a rookie please.

thanks in advance

PaulCombining multiple databases into a single database is not that hard, although it is rather cumbersome and labor intensive. I have combined 28 databases into one database using MS-SQL 6.5.

The "real skinny" is complex, much more than I'm comfortable doing in a single post, but I can give you the 30,000 foot view fairly easily.

Create a whole new database for the purposes of conversion ONLY. Use your present table structures, but add two new columns to each table... One of those new columns should be a GUID as the unique key that will be used in the future, the other should be an indicator for which of the "old" databases that the row originated from...

The Primary Key going forward should be the GUID. The alternate key used for the purposes of conversion should be the system of origin and whatever the PK was on the old database.

Once you get the GUID ids in place in the conversion copy and get the PK and FK values straight, then it is simple to copy the conversion data into the "new" database. Because GUID values are unique across systems, it doesn't matter when you move them from one machine/server/database/whatever to another.

While you are doing this conversion, you might also want to consider converting all of your date and time values to UCT. This allows you to correctly display local time for anyone in the world, allowing you to ignore timezone problems too.

-PatP|||Hi Pat

thank you very much for your great reply.

Okay, so I understand most of what you said. But please understand I'm not an I.T tech person. My job is an Information Analyst. I have a strong interest in databases and desperately want to learn more.

Firstly, you say I have to create a new db. That is fine, I can do that. For the purposes of conversion, do you mean by that, somewhere to put the new data? or something else?

I had to look up what a GUID was. Globally unique ID. Ok, I get that. Although I don't know how I'm going to get these GUIDs into the new db. I dont know how.

"The Primary Key going forward should be the GUID. The alternate key used for the purposes of conversion should be the system of origin and whatever the PK was on the old database.

Once you get the GUID ids in place in the conversion copy and get the PK and FK values straight, then it is simple to copy the conversion data into the "new" database. Because GUID values are unique across systems, it doesn't matter when you move them from one machine/server/database/whatever to another"

I kind of understand this. But I don't know how to actually do it.

You mentioned this is just some outline of a "how to", can you point me in the right direction to see the "in depth" version if it exists.

thanks again
Paul

Combining multiple databases into a single database is not that hard, although it is rather cumbersome and labor intensive. I have combined 28 databases into one database using MS-SQL 6.5.

The "real skinny" is complex, much more than I'm comfortable doing in a single post, but I can give you the 30,000 foot view fairly easily.

Create a whole new database for the purposes of conversion ONLY. Use your present table structures, but add two new columns to each table... One of those new columns should be a GUID as the unique key that will be used in the future, the other should be an indicator for which of the "old" databases that the row originated from...

The Primary Key going forward should be the GUID. The alternate key used for the purposes of conversion should be the system of origin and whatever the PK was on the old database.

Once you get the GUID ids in place in the conversion copy and get the PK and FK values straight, then it is simple to copy the conversion data into the "new" database. Because GUID values are unique across systems, it doesn't matter when you move them from one machine/server/database/whatever to another.

While you are doing this conversion, you might also want to consider converting all of your date and time values to UCT. This allows you to correctly display local time for anyone in the world, allowing you to ignore timezone problems too.

-PatP