How to quickly export data from R to SQL Server

sql sql-server r bigdata
jpd527 picture jpd527 · Oct 4, 2013 · Viewed 35k times · Source

The standard RODBC package's sqlSave function even as a single INSERT statement (parameter fast = TRUE) is terribly slow for large amounts of data due to non-minimal loading. How would I write data to my SQL server with minimal logging so it writes much more quickly?

Currently trying:

toSQL = data.frame(...);
sqlSave(channel,toSQL,tablename="Table1",rownames=FALSE,colnames=FALSE,safer=FALSE,fast=TRUE);

Answer

jpd527 picture jpd527 · Oct 4, 2013

By writing the data to a CSV locally and then using a BULK INSERT (not readily available as a prebuilt function akin to sqlSave), the data can be written to the MS SQL Server very quickly.

toSQL = data.frame(...);
write.table(toSQL,"C:\\export\\filename.txt",quote=FALSE,sep=",",row.names=FALSE,col.names=FALSE,append=FALSE);
    sqlQuery(channel,"BULK
                INSERT Yada.dbo.yada
                FROM '\\\\<server-that-SQL-server-can-see>\\export\\filename.txt'
                WITH
                (
                FIELDTERMINATOR = ',',
                ROWTERMINATOR = '\\n'
                )");

SQL Server must have permission to access the network folder holding the CSV file, or else this process will not work. While it takes some setup with various permissions (the network folder and BULK ADMIN privileges, the reward in speed is infinitely more valuable).

Related questions

RODBC sqlSave table creation problems

I'm having trouble creating a table using RODBC's sqlSave (or, more accurately, writing data to the created table). This is different than the existing sqlSave question/answers, as the problems they were experiencing were different, I can create tables whereas …

Read More
Unable to append to SQL Server table using sqlSave in R

I am trying to update a SQL table using sqlSave function of RODBC package in R. Data is present in a data frame. When I try to run the command: sqlSave(DBConn, dat=df, verbose=T, tablename='table', append=T) …

Read More
RODBC Temporary Table Issue when connecting to MS SQL Server

I am running R on unix and I am using the RODBC package to connect to MS SQL server. I can execute a query that returns results fine with the package, but if I use a temporary table somewhere in …

Read More