How to Execute the SQL Query?

Question

Hi,

We have 2 databases 

Ex:- server1 - database1 - table1  ( empno, ename, deptno )

       server2 - database2 - table2  (deptno, dname, loc )

SQL Query:- select table1.*, Table2.* from "[database1].[dbo].[table1]"  "A"  join "[database2].[dbo].[table2]"  "B" on A.deptno=B.deptno; 

Can we Execute this SQL?

If Yes, where we will execute the Query (Server1 or Server2) 

Answer 1

Hi Vamsi,

You need to have a linked server either one of your sql server.

for example you have Server1 as a linked server in Server2 then you need to execute query on Server2

select table1.*, Table2.* from "[server1].[database1].[dbo].[table1]"  "A"  join "[database2].[dbo].[table2]"  "B" on A.deptno=B.deptno

---

Thanks & Regards Prasad DVR

Answer 2

You need to create linked server first and then use the following syntax to run it in the instance with the linked server to point to the other:

select A.*, B.* from Server1.[database1].[dbo].[table1] As A  
join Server2.[database2].[dbo].[table2] as B on A.deptno=B.deptno; 

Answer 3

Thanks DVR & Jingyang Li