Re: Suggestions?: Querying tables from multiple databases

Steve (stevesusenet@yahoo.com)  writes:
> I am looking for suggestions about how to solve a problem using tsql.
>
> I have been asked to create a report concerning 4 tables.
>
> Each of the 4 tables is in its own database.
>
> The 4 tables are identical in name and structure.
>
> I would like to query all 4 of these tables and get the results as if
> they were one table.

In additions to MCFoster's posting, you can combine these four
tables with a UNION query:

SELECT ....
FROM  db1.dbo.tb11
UNION ALL
SELECT ....
FROM  db2.dbo.tb11
UNION ALL
..

You can also create a view that comprises this SELECT statement, and refer
to that in the query.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp