How to export data from R to SQL Server quickly

1059320 pts.
Tags:
Big Data
SQL Server
Is there a way I can export data from R to SQL Server quickly? The standard way (SQLSave) is really slow for a large amount of data. This is what I tried so far:
toSQL = data.frame(...);
sqlSave(channel,toSQL,tablename="Table1",rownames=FALSE,colnames=FALSE,safer=FALSE,fast=TRUE);
Thank you!

Answer Wiki

Thanks. We'll let you know when a new response is added.

Hi ITKE,

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
‘\\\\\\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).

I hope
it will assist you in resolving issues.

Thanks
& Regards

Clark
Kent

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • ToddN2000
    Thanks for the tip Clark!
    This may be handy for users looking to migrate a lot of large files.

    Nice to know there is a good performance boost in the method you suggest.
    94,885 pointsBadges:
    report
  • mateskabe
    Try to use RSQLS package: https://github.com/martinkabe/RSQLS

    Very fast pushes data from data.frame to SQL Server or pulls from SQL Server to data.frame.

    **Example:**
        
        library(devtools)
        install_github("martinkabe/RSQLS")
        library(RSQLS)

        cs <- set_connString("LAPTOP-USER\\SQLEXPRESS", "Database_Name")
        push_data(cs, dataFrame, "dbo.TableName", append = TRUE, showprogress = TRUE)
        df <- pull_data(cs, "SELECT * FROM dbo.TableName", showprogress = TRUE)

    This solution is much faster and more robust than **RODBC::sqlSave** or **DBI::dbWriteTable**.
    10 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: