查询同一服务器上不同数据库中的两个表

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上的两个不同数据库中查询两个表。在一个表上，我需要获取所有行（简单选择），另一个是select，但是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

4 个解决方案

#1

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_UserObject which 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.

你确定你正确拼写“中心”吗？我注意到你把它拼写为IIPCentre_UserObject中的“中心”，我认为它可能适合英国（？），但你拼写为QUALITY_CENTER的“中心”。我认为它在你的环境中以某种方式拼写。

#2

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是您的第二个数据库。

#3

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

#4

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

#1