FreeKB - Microsoft SQL Server Copy records from one table to another table
Microsoft SQL Server - Copy records from one table to another table

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.

Server1\Instance1 SQLNCLI SQL Server Instance1 NULL NULL NULL


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


Add a Comment

We will never share your name or email with anyone. Enter your email if you would like to be notified when we respond to your comment.

Please enter 772e4 in the box below so that we can be sure you are a human.


Web design by yours truely - me, myself, and I   |   |