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
go

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
318 views
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]
GO

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

 

Good Luck!

 


Answered January 30, 2017
Your Answer