Querying a different SQL Server instance - syntax

Question

Hi guys,

What is the query syntax to query a different SQL Server instance on the same server?

Imagine I have something like this:

Instance 1: SUBZERO\STAGING
Instance 2: SUBZERO\DWH

When I open a new query window, the connection is associated with one of the instances. How do I join database tables from both instances?

Thank you

Answer 1

You can create "Linked Server" pointing to the other instance, then you can use a 4-part-qualifier to access the other SQL Server, like

select * from servername.databasename.schema.tablename  

See
https://learn.microsoft.com/en-us/sql/relational-databases/linked-servers/linked-servers-database-engine?view=sql-server-ver15
https://learn.microsoft.com/en-us/sql/relational-databases/linked-servers/create-linked-servers-sql-server-database-engine?view=sql-server-ver15

Answer 2

Hi，@Pedro Soares

Welcome to Microsoft T-SQL Q&A Forum!

you should create a linked server first , and use the Server.Catalog.Schema.Table name to refer to tables in other instances . Use Inner join, like this:

SELECT *  
FROM server1_table t1  
INNER JOIN server2.database.dbo.server1_table  as t2  
ON t2.column=t1.column  

Best regards,
Bert Zhou

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Just to be clear.

SQL Server does not know the other instance exists. There is no difference between connecting to a different instance on different server and the same server. You need to use linked servers to connect to another instance.

Answer 4

Hi Olaf,

Thank you, that makes sense. I tried that before I posted the question but it seems my user doesn't have permissions to add Linked Servers, so I couldn't test it properly.

I'll mark it as Answer as soon as I can confirm :-)

Best regards