Suggestions?: Querying tables from multiple databases

[Steve]

Hi;

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.

All suggestions welcome, thanks.

Steve

[MGFoster]

Steve wrote:

Hi;

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.

All suggestions welcome, thanks.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Are all the databases on the same SQL Server? If so you can refer to
each table using this format:

database_name.owner.table_name

E.g.:

Accounts.dbo.TableOfAccounts

If the other databases are remote you can use the linked server feature
of SQL'r and refer to the other DBs using the linked server alias name.
See the BOL article "Adding a Linked Server" and related articles for
more info.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQpN05YechKqOuFEgEQKl3gCgip9nRMQ3FWWGFaKQXynjLI iH+owAn11s
KtkkGhGJHroLQJsfHhQL7gaO
=nk3A
-----END PGP SIGNATURE-----

[Erland Sommarskog]

Steve (st**********@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, es****@sommarskog.se

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