跨同一服务器上的多个数据库查询 [英] Query across multiple databases on same server
问题描述
我正在寻找一种处理以下情况的方法:
I am looking for a way of dealing with the following situation:
我们有一个数据库服务器，上面有多个数据库(都具有相同的架构，不同的数据).
We have a database server with multiple databases on it (all have the same schema, different data).
我们正在寻找一种在所有数据库中进行查询的方法(并且使其易于配置，因为可以随时添加更多数据库).这种数据访问必须是实时的.
We are looking for a way to query across all the databases (and for it to be easy to configure, as more databases may be added at any time). This data access must be realtime.
举个例子，你有一个插入订单的应用程序——每个应用程序都有自己的数据库等.然后我们正在寻找一种有效的方式，让单个应用程序访问所有其他数据库中的订单信息以便查询它并随后对其进行操作.
Say, as an example, you have an application that inserts orders - each application has its own DB etc. What we are then looking for is an efficient way for a single application to then access the order information in all the other databases in order to query it and subsequently action it.
到目前为止，我的搜索并没有透露太多信息，但是我想我可能只是错过了合适的关键字才能找到正确的信息...
My searches to date have not revealed very much, however I think I may just be missing the appropriate keywords in order to find the correct info...
推荐答案
它不会是有史以来最干净的解决方案，但您可以在主数据库"上定义一个视图(如果您的各个数据库不会保持不变) 包含来自各个数据库的数据，并允许您对单个源执行查询.
It's not going to be the cleanest solution ever, but you could define a view on a "Master database" (if your individual databases are not going to stay constant) that includes the data from the individual databases, and allows you to execute queries on a single source.
例如...
CREATE VIEW vCombinedRecords AS
SELECT * FROM DB1.dbo.MyTable
UNION ALL
SELECT * FROM DB2.dbo.MyTable
这允许你做...
SELECT * FROM vCombinedRecords WHERE....
当您的数据库发生变化时，您只需更新视图定义以包含新表.
When your databases change, you just update the view definition to include the new tables.
这篇关于跨同一服务器上的多个数据库查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
