SQL 查询同一服务器上不同数据库中的两个表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6944373/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Query against two tables in separate databases on the same server
提问by GurdeepS
I need to query two tables in two different databases on the same SQL Server. On one table, I need to get all the rows (simple select) and on the other, a select but where the id matches a parameter in my stored proc.
我需要在同一个SQL Server. 在一张表上，我需要获取所有行（简单选择），而在另一张表中，需要获取一个选择，但 id 与存储过程中的参数匹配。
I tried doing this but get the error
我尝试这样做但得到错误
The multi-part identifier could not be bound.
无法绑定多部分标识符。
How can I go about this?
我该怎么办？
QUERY:
询问：
SELECT QUALITY_CENTER, POSTCODE_ID, (SELECT [QCID]
FROM [Website_Interactive].[dbo].[IIPCentre_UserObject]
WHere LoginID = @loginID)
FROM IIP_QC_LIST
回答by Abe Miessler
Sounds like you mistyped something. You can query a table in another DB using the following method:
听起来你打错了一些东西。您可以使用以下方法查询另一个数据库中的表：
SELECT tn.ID, tn.NAME
FROM [Database Name].[Schema].[TableName] as tn
I purposely added a two word database name because you have to put square brackets around that for it to be recognized. Your Schema will most likely be dbo.
我特意添加了一个两个词的数据库名称，因为您必须在其周围加上方括号才能识别它。您的架构很可能是dbo.
If you show us your query and give us the DB names I can provide a more complete answer.
如果您向我们展示您的查询并向我们提供数据库名称，我可以提供更完整的答案。
UPDATE:
更新：
Are you sure you are spelling "Center" correctly? I noticed you spelled it "centre" in IIPCentre_UserObjectwhich I think might be right for the UK (?) but you spelled it "center" for QUALITY_CENTER. I would assume it's spelled one way or the other in your environment.
您确定正确拼写“Center”吗？我注意到您将其拼写为“中心” IIPCentre_UserObject，我认为这可能适合英国 (?) 但您将其拼写为“中心”以表示QUALITY_CENTER. 我认为它在您的环境中以一种或另一种方式拼写。
回答by Ayyoudy
You can easily do that by providing the FQN (Fully Qualified Name) to the SQL object (in this case your SQL table). The FQN syntax for a table is as such:
您可以通过向 SQL 对象（在本例中为您的 SQL 表）提供 FQN（完全限定名称）来轻松地做到这一点。表的 FQN 语法如下：
[database-name].[schema-name].[table-name]
Example:
例子：
SELECT a, b, c FROM Database1.Schema1.Table1
UNION
SELECT a, b, c FROM Database2.Schema2.Table2
Where Database1 is your first database and Database2 is your second.
其中 Database1 是您的第一个数据库，Database2 是您的第二个。
回答by Code Magician
It's possible/straightforward to select from different databases on the same server. You need to use a fully qualified name i.e.
可以/直接从同一服务器上的不同数据库中进行选择。您需要使用完全限定的名称，即
SELECT * from database.schema.table
For example
例如
SELECT * FROM northwind.dbo.orders where id = @id
回答by James Tubiano
You can query two separate database if the table from 1 database is the same value with another table
如果来自 1 个数据库的表与另一个表的值相同，则可以查询两个单独的数据库
like these:
像这些：
SELECT * FROM DB1.dbo.MyTable db1,DB2.dbo.MyTable db2 where db1.table1=db2.table1
