Database Systems Help

dinologic · April 14 '03, 02:41 PM (#1)

I'm finally taking the plunge to SQL 2K. I've been working with Access for a few years and have accumulated several databases all over our network. One of those databases contains information that is used by the others through linked tables. When I move to SQL, how should I handle this? Can you query more than one database with one query? Is there a similar feature in SQL Server to allow one database to be "linked" to another? Or should I just create one huge database? (By "huge" I mean many tables, but record-wise, the largest table probably takes on about 30-40k records each year.)

Any info here would be much appreciated.

Thanks.

smoseley · April 14 '03, 05:14 PM (#2)

You can have "linked servers", which will treat multiple physical (or logical) database servers as one machine. When you get to building your query, though, you will have to reference cross-database queries like this:

Code:

SELECT
    p.name,
    a.address
FROM
    human_resources.dbo.person AS p
    INNER JOIN mailing_room.dbo.address AS a
        ON p.ssn = a.ssn

In this query, the name "human_resources" or "mailing_room" would represent your database (aka catalog) name. The "dbo" would represent the object owner (the user role in the database who owns the object. "dbo" is default. You can also leave it blank for dbo - i.e. "human_resources..person"). The "person" or "address" would represent the name of the table. "p" and "a" are aliases to the tables, and so on.

This assumes that all of your databases (catalogs) are on one server or multiple linked servers.

Database Systems Help

From ACCESS to SQL Server 2K