Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Query against two tables in separate databases on the same server

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.

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
like image 293
GurdeepS Avatar asked Aug 04 '11 15:08

GurdeepS


People also ask

Can I query from two different databases?

Multiple Databases on One Server InstanceIt is possible to use SQL to write one query that combines the information from many databases. There are two requirements: You must prefix all table references with the database name (e.g. customers.

Can we join two tables from different databases in SQL Server?

SQL Server allows you to join tables from different databases as long as those databases are on the same server. The join syntax is the same; the only difference is that you must fully qualify table names.

How do I run the same query on multiple databases in SQL Server?

There is a handy undocumented stored procedure that allows you to do this without needing to set up a cursor against your sysdatabases table in the master database. This can be done by using sp_MSforeachdb to run the same command in all databases.


3 Answers

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.

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.

like image 56
Abe Miessler Avatar answered Oct 11 '22 16:10

Abe Miessler


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:

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

like image 33
Ayyoudy Avatar answered Oct 11 '22 18:10

Ayyoudy


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
like image 24
Code Magician Avatar answered Oct 11 '22 18:10

Code Magician