Let's say you have SQL Server running on two different servers in your network, such as Server1\Instance1 and Server2\Instance2, and you want to copy all of the records in a table from Server1\Instance1 to Server2\Instance2.  On Server2, ensure you can connect to both Server1\Instance1 and Server2\Instance2 using SQL Server Management Studio.

On Server2\Instance2, add Server1\Instance1 as a linked server. You may need to sign into SQL Server as admin using Windows Authentication to perform this step.

execute sp_addlinkedserver 'Server1\Instance1'

Confirm Server1\Instance1 was added as a linked server.

execute sp_linkedservers

A table should be displayed listing the linked server.

If you are signed into Server2\Instance2 as admin using Windows Authentication, disconnect from the server and then reconnect using SQL Server Authentication. It is helpful to ensure that both Server1\Instance1 and Server2\Instance2 have an account with an identical username and password. To confirm you can communicate with the linked server, select data from Server1\Instance1.

select * from [Server1\Instance1].database_name.dbo.table_name

If you are unable to select data from Server1\Instance1, try the following:

• Ensure Windows Firewall on both Server1\Instance1 and Server2\Instance2 are allowing traffic on both port 1433
• Ensure the SQL Server Browser service is running on both Server1\Instance1 and Server2\Instance2

Once you are able to select data from Server1\Instance1, you should have no problem copying data from Server1\Instance1 to Server2\Instance2.

insert into table_name (column1, column2, column3)
select column1, column2, column3 from [Server1\Instance1].database_name.dbo.table_name