Skip to main content

What is the best way to move data across linked servers in SQL Server?

I tried this query:

on mylocalsqlserver:
Use mydb

Insert into [dbo].[Countries]
select cntNameISU from [myremotelinkedserver].[thatdb].[dbo].[tblcountries]

It takes a long time to copy the data. I am moving 200k records. Is there a better and faster way to do this?

Asked January 30, 2017
Posted Under: SQL Server
1 Answers

Yes, there is. If you are moving a lot of records, it is best to use SSIS, if the process is an ongoing one.

However, if it is a one-time deal, or you want to run manually, then you can do this, referring to your example above:

on mylocalserver:

USE [mydb]

INSERT INTO [dbo].[Countries]
     select * from openquery(myremotelinkedserver,'select cntNameISU from [thatdb].[dbo].[tblcountries]')


Good Luck!


Answered January 30, 2017
Your Answer