Query for two tables in separate databases on one server

Question

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 (a simple choice), and on the other I need to select, but where id matches the parameter in my saved proc.

I tried to do this, but we get an error

A multi-part identifier cannot be bound.

How can i do this?

QUERY

  SELECT QUALITY_CENTER, POSTCODE_ID, (SELECT [QCID] FROM [Website_Interactive].[dbo].[IIPCentre_UserObject] WHere LoginID = @loginID) FROM IIP_QC_LIST 

Answer 1

It seems like you were saddened by something. You can query the table in another database using the following method:

 SELECT tn.ID, tn.NAME FROM [Database Name].[Schema].[TableName] as tn 

I intentionally added a two-word database name, because you need to put square brackets around so that it is recognized. Most likely your circuit will be dbo .

If you show us your request and give us the database names, I can provide a more complete answer.

UPDATE:

Are you sure you wrote Center correctly? I noticed that you specified a “center” in IIPCentre_UserObject , which I think might be right for the UK (?), But you wrote a “center” for QUALITY_CENTER . I would suggest that it is written anyway in your environment.

Answer 2

You can easily do this by providing an FQN (fully qualified name) to the SQL object (in this case, your SQL table). The FQN syntax for the table is as follows:

 [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 the second.

Answer 3

It is possible / just to choose from different databases on the same server. You need to use the full name, i.e.

 SELECT * from database.schema.table 

for example

 SELECT * FROM northwind.dbo.orders where id = @id 

Answer 4

You can query two separate databases if a table from 1 database matches another table

such as the:

 SELECT * FROM DB1.dbo.MyTable db1,DB2.dbo.MyTable db2 where db1.table1=db2.table1