none
Exporting data from R to SQL Server RRS feed

  • Question

  • Hi all,

    I am using the RevoScaleR function rxImport to pull data from SQL Server to an R memory frame via ODBC.

    Apparently, however, there is no way to go in the other direction, i.e., push an R memory frame into a SQL Server database.

    Does anyone know what is the reason for this asymmetry?

    If I want to INSERT data into a SQL Server table from R, do I need to resort to an external package such as RODBC, or there is some workaround to do it within RevoScaleR?

    Thank you in advance!

    Gianluca

    Thursday, December 15, 2016 11:45 AM

All replies

  • Hi Gianluca,

    You can use the rxDataStep and RxOdbcData for that purpose. Please check the documentation at https://msdn.microsoft.com/en-us/microsoft-r/scaler-odbc and in the package help.

    Thanks

    Sumit

    Tuesday, February 21, 2017 10:28 PM
  • Since insert INTO is limited to 1000 rows, you can dbBulkCopy from rsqlserver package.

    dbBulkCopy is a DBI extension that interfaces the Microsoft SQL Server popular command-line utility named bcp to quickly bulk copying large files into table. For example:

    url = "Server=localhost;Database=TEST_RSQLSERVER;Trusted_Connection=True;"
    conn <- dbConnect('SqlServer',url=url)
    ## I assume the table already exist
    dbBulkCopy(conn,name='T_BULKCOPY',value=df,overwrite=TRUE)
    dbDisconnect(conn)




    OR


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


    MY BOOK

    Friday, February 24, 2017 2:18 AM