CODEWITHSUNDEEP
sqlsql-servert-sqltransactions
ShadyBears
ShadyBears

Reputation: 4185

How to switch server connection in SQL Server?

I have a transaction that inserts into two different tables that are on two different servers. I want to do some insertions and then switch to another server and do some more insertions. How do I achieve this via SQL?

I apologize if this is a duplicate but I could not find my answer via StackOverflow or Google. Thank you!

I'm using SQL Server 2016 and Management Studio

Upvotes: 3

Views: 12257

Answers (2)

RoMEoMusTDiE
RoMEoMusTDiE

Reputation: 4824

https://learn.microsoft.com/en-us/sql/relational-databases/linked-servers/create-linked-servers-sql-server-database-engine

-- create

USE [master]  
GO  
EXEC master.dbo.sp_addlinkedserver   
    @server = N'<SQLMachine>\<instance>',   
    @srvproduct=N'SQL Server' ;  
GO


EXEC master.dbo.sp_addlinkedsrvlogin   
    @rmtsrvname = N'<SQLMachine>\<instance>',   
    @locallogin = NULL ,   
    @useself = N'True' ;  
GO

--- test

SELECT name FROM [<SQLMachine>\<instance>].master.sys.databases ;  
GO 


SELECT name FROM [<SQLMachine>\<instance>].<databasename>.<schema>.tablename> ;  
GO 

SELECT name FROM [MyServer].MyDB.dbo.myTable ;  
GO

Upvotes: 1

LONG
LONG

Reputation: 4620

right click anywhere in your current SQL query page, Connection -> Change connection..

OR

right click the new server instance, New Query

update :

if those two servers are linked together, then you could use distributed query or openquery to achieve that.

For example: on server1: select * From server2.database.dbo.table --four part distributed query

or on server1: select * From openquery(server1,'select * from database.dbo.table') --openquery

Upvotes: 8

Related Questions