How to copy records from one SQL Server table to another SQL Server table

Home > Search > How-to
  by

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.

SRV_NAME SRV_PROVIDERNAME SRV_PRODUCT SRV_DATASOURCE SRV_PROVIDERSTRING SRV_LOCATION SRV_CAT
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 in the box below so that we can be sure you are a human.




Comments