Microsoft SQL Server forum - Re: Suggestions?: Querying tables from multiple databases

You are viewing an archived Text-only version of the thread. To view this thread in it's original format and/or if you want to reply to this thread please [click here]

Author

Re: Suggestions?: Querying tables from multiple databases

Erland Sommarskog

2005-05-24, 8:24 pm

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

Sponsored Links