mssqltips logo

Copy data to another SQL Server instance without a Linked Server

By:   |   Updated: 2017-03-01   |   Comments (1)   |   Related: More > Import and Export

Problem

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?

Solution

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;
go

create table dbo.t (a int, b varchar(100), d datetime);
go
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'
go

-- 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;
go
create user xyz for login xyz;
alter role [db_datareader] ADD MEMBER [xyz]; -- give it db_datareader role
go

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

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

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


-- 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;
go
create user xyz for login xyz;
go
alter role [db_datareader] ADD MEMBER [xyz]; -- give it db_datareader role
alter role [db_datawriter] ADD MEMBER [xyz]; -- give it db_datawriter role
go

Implementation

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;
go

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.

Summary

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


Last Updated: 2017-03-01


get scripts

next tip button



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.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Wednesday, March 01, 2017 - 10:08:32 AM - David Back To Top

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. 

 

 



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools