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
Best Answer
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
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' ; GOEXEC 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