Select from multiple database - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Select from multiple database

Author

Topic

dat2199
Starting Member

4 Posts

Posted - 2002-10-14 : 08:35:32

Is there anyway I can select from multiple database.

Example
I ve two database A & B. A contains table X and B contains table Y.
I would like to select records from table X and table Y.

[URL=http://www.getwhatever.com]www.getwhatever.com[/URL]

philh
Starting Member

18 Posts

Posted - 2002-10-14 : 12:15:45

If the databases are on the same server:

SELECT * from A.DBO.X, B.DBO.Y

If the databases are on different servers:

SELECT * from SERVER1.A.DBO.X, B.DBO.Y

(where B resides on the local server; you'll need a link server to SERVER1)

Of course you can do joins, etc. with this approach.

Read BOL for an explanation of SQL's dot notation.

Phil Hegedusich
Senior Web Developer
IIMAK
http://www.iimak.com
The best thermal transfer printer ribbons on the planet

Edited by - philh on 10/14/2002 12:16:47

dat2199
Starting Member

4 Posts

Posted - 2002-10-15 : 02:17:56

Thanks Philh!
Do you think it is efficient to separate database or combine tables into one database?

For example, I ve two database - COMMON and COMPANY.
COMMON consist a table called COUNTRY.
COMPANY consist a table called STAFF which make use of table COUNTRY (Relationship).

Do u think it is efficient to put table COUNTRY & STAFF into one database or separate them? I may ve another database called PAYMENT which make use of the table COUNTRY (Relationship).

[URL]www.getwhatever.com[/URL]

Crespo

85 Posts

Posted - 2002-10-15 : 07:13:48

quote:

Do you think it is efficient to separate database or combine tables into one database?

I would create ALL the tables in the same database. Why do you want to have them in separate databases?

If all three join together, you are only adding further complicatins by having them on separate databases!

You should see me here! doing 2 migrations on the same database... Ooops!

Good Luck!

Crespo.
Hewitt Bacon & Woodrow
Epsom
Surrey
United Kingdom

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources