Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
I want to know how to execute sql query from two database, one database is a remote server and next one is my local database, so from both the database table I want to use JOIN query.
Is it possible to do in SQL server management studio 2016 ?
If it is possible please guide how to do so.
Thanks, Manu.
As @Akina mentioned linked server might be a way to go. if you create linked server with name RemoteServer your query may look like:
Select * from myLocalDatabase.dbo.myLocalTable mlt
join RemoteServer.RemoteDatabase.dbo.myRemoteTable mrt
on mlt.col1 = mrt.col1
Be aware of performance penalty when running remote joins. in certain cases you might be better importing data to local server and then joining it especially if this is going to repeat more often.
As for creation of linked server you could try this to add another SQL Server
USE [master]
GO
EXEC master.dbo.sp_addlinkedserver @server = N'RemoteServerDescription', @srvproduct=N'', @provider=N'SQLNCLI11', @datasrc=N'RemoteServername_or_IP', @catalog=N'databasename'
USE [master]
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'RemoteServerDescription', @locallogin = NULL , @useself = N'True'
GO
