如何快速将数据从R导出到SQL Server

问题描述

标准RODBC程序包的sqlSave函数(即使是单个INSERT语句(参数fast = TRUE))由于非最小加载而对于大量数据来说也非常慢.我该如何以最少的日志记录将数据写入SQL服务器，从而使其写入速度更快?

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?

当前正在尝试:

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

推荐答案

通过在本地将数据写入CSV，然后使用BULK INSERT(类似于sqlSave的预构建函数不易使用)，数据可以很快就会被写入MS SQL Server.

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必须具有访问包含CSV文件的网络文件夹的权限，否则此过程将无法进行.尽管它需要进行各种设置才能获得各种权限(网络文件夹和BULK ADMIN特权，但速度的回报是无价之宝).

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

这篇关于如何快速将数据从R导出到SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！