Copy data to another SQL Server instance without a Linked Server

By:   |   Comments (2)   |   Related: More > Import and Export


Copying data from a table in one instance to a table in another instance is a common task. You could do this with T-SQL and a Linked Server, but what if you can't use a Linked Server. To make it more difficult, say I have three servers: A, B, C and I want to copy data from a table on A to a table on B by running a T-SQL script on C, how can I do that?


There are many ways one can copy data across instances, the most common are:

These methods all have their pros and cons. To answer the question of copying tables between different instances by running a T-SQL on a separate centralized instance without Linked Servers, the only alternative way is to use an SSIS Package and create a SQL Server Agent Job to execute it, but this is tedious in both implementation and maintenance.

With R integration in SQL Server 2016, this difficult question has a perfect solution now.

Algorithm Description

SQL Server R service has included a default R package RevoScaleR, which contains lots of functions that are helpful for loading data from various sources, such as the following:

  • Using RxSQLServerData to create two objects each connecting the source and the target
  • Using RxDataStep to dump the data from the source to the target.

We will see how we can use these to solve our problem.

Environment Setup

There are three SQL instances on my computer, the default instance is SQL Server 2012, another is [localhost\sql2014] and the last is [localhost\sql2016]. What I will do is to run the T-SQL script on [localhost\sql2016] and copy data from default instance [localhost] to [localhost\sql2014] instance and [localhost\sql2014].

On [localhost], I will create a simple table and populate it with a few records

--on [localhost], i.e. source instance
use TestDB
if object_id('dbo.t', 'U') is not null
 drop table dbo.t;

create table dbo.t (a int, b varchar(100), d datetime);
insert into dbo.t (a, b, d)
select 1, 'hello', '2017-01-01'
union all
select 2, 'world', '2017-01-02'
union all
select 3, 'happy FireRooster year', '2017-01-28'

-- assume we have created a sql login on this [localhost] instance
Use TestDB
--drop user xyz;
--drop login xyz;

create login [xyz] with password='pa$$W0rD_xyz', check_policy=off;
create user xyz for login xyz;
alter role [db_datareader] ADD MEMBER [xyz]; -- give it db_datareader role

On the target instance, we set up the destination table.

-- on [loclahost\sql2014], i.e. the destination instance
if object_id('dbo.t', 'U') is not null
 drop table dbo.t;

create table dbo.t (a int, b varchar(100), d datetime);

-- also assume, we have created a sql login on this [localhost\sql2014] instance
use TestDB;
--drop user xyz;
--drop login xyz;
create login [xyz] with password='pa$$W0rD_XyZ', check_policy=off;
create user xyz for login xyz;
alter role [db_datareader] ADD MEMBER [xyz]; -- give it db_datareader role
alter role [db_datawriter] ADD MEMBER [xyz]; -- give it db_datawriter role


The coded needed is relatively simple as shown below:

-- run on the central instance, i.e. [localhost\sql2016]
-- declare variables, so we can change the source/target tables on the fly when doing the 
declare @src_conn varchar(300), @dest_conn varchar(300);
declare @src_tbl varchar(200), @dest_tbl varchar(200);

-- set variables
select @src_conn = 'server=localhost; database=TestDB; UID=xyz; PWd=pa$$W0rD_xyz;'
, @dest_conn = 'server=localhost\sql2014; database=TestDB; UID=xyz; PWd=pa$$W0rD_XyZ;'
, @src_tbl = 'dbo.t'
, @dest_tbl = 'dbo.t';

-- run the R code inside t-sql
exec sp_execute_external_script @language = N'R'
, @script = N'
indata <- RxSqlServerData(table = src_tbl, connectionString = src_conn) # when src and target columns have same names otherwise make some modification
outdata <- RxSqlServerData(table = dest_tbl, connectionString = dest_conn);
rxDataStep(inData = indata, outFile = outdata, append =  "rows")'
, @params = N'@src_conn varchar(300), @dest_conn varchar(300), @src_tbl varchar(200), @dest_tbl varchar(200)'
, @src_conn = @src_conn
, @dest_conn = @dest_conn
, @src_tbl = @src_tbl
, @dest_tbl = @dest_tbl
with result sets none;

After running the script in a SSMS query window, we will see the following:

copy execution result

In [localhost\sql2014], we can see that 3 records have been copied from the source table.

target table result

Limitations and Workarounds

R integration with SQL Server is still in its early stages, so there is no surprise there were lots of limitations during my research.

  1. The first limitation is both the source table and the target table should have the exact same column names. Otherwise, there will be error like the following.

    Error when column names are different

    If we do not have the same column names, we can make a change to indata database source as follows. For example, the source table has columns [a], [b], [c] and the target table has columns [x], [y], [z], then we can alias the source columns as the following:

    indata <- RxSqlServerData(sqlQuery="select x=a, y=b, z=c from [src table]", connectionString = src_conn)
  2. The second limitation is that many current SQL data types are not supported, such as XML, varbinary etc, for details see [Restrictions] section.

    There is no alternative here. The only way to by-pass it is to run the R code in its native environment (instead of inside T-SQL), for example, you run R code in RStudio IDE.

  3. The third limitation is that if the target table has an identity column, there is no way to turn the identity insert on inside R code.

    An alternative is that we load data into a staging table without identity column and later load data from staging to the target table via T-SQL.

One Advantage

There is one specific feature I like most about RxDataStep. In this function, there is a parameter overwrite, if it is set to True, the function can automatically create a target table if it is not there.  This is very helpful when we want to just load data into the target instance as staging tables, which we do not need to create beforehand.


In this tip, we explored a way to copy table data across different SQL instances by running pure T-SQL on a central server, and this tip can be easily customized to do other types of data loading, such as reading CSV file, Excel file, or any ODBC accessible data sources and then writing to destination. In some way, this can be considered a T-SQL command line version of some of the SSIS features.

With T-SQL as a hosting language for R (hopefully Python or others in vNext SQL Server), T-SQL may be more than just a language, it could be a platform.

Although the R language is mainly for data science work, it still brings lots of benefits to DBAs. In some way, it opens up another dimension for database administration and makes some previously tedious easier and some impossible tasks possible.

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Jeffrey Yao Jeffrey Yao is a senior SQL Server consultant, striving to automate DBA work as much as possible to have more time for family, life and more automation.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Monday, March 2, 2020 - 1:51:38 AM - Nilesh Chauk Back To Top (84873)

Great article. explained very well, but how it will be a limitation as you mentioned as first point in "Limitations ..".

It is a necessity for it.

Can we use this in SQL Server 2014? I think, this feature is available from 2016.


Wednesday, March 1, 2017 - 10:08:32 AM - David Back To Top (46838)

Thanks for the article.  Very interesting.

I would suggest that another common method for moving data is to script the data as insert statements.  This is an excellent solution:

- if you do not have too many rows (we have used it even in cases where there are 10's of thousands of rows)

- it is a repeatable process - You can use the same script in test and prod.

- can be taught to developers and DBAs and can be done with minimal privileges. 



get free sql tips
agree to terms