如何将R数据帧插入SQL Server中的现有表 [英] How to insert R dataframe into existing table in SQL Server

问题描述

尝试了一些在线找到的不同程序包和方法后，我还没有找到一种解决方案，该解决方案可用于将R中的数据帧插入SQL Server中的现有表中. 我使用MySQL取得了很大的成功，但是SQL Server似乎更加困难.

After trying a few different packages and methods found online, I am yet to find a solution that works for inserting a dataframe from R into an existing table in SQL Server. I've had great success doing this with MySQL, but SQL Server seems to be more difficult.

我设法使用DBI包编写了一个新表，但是我找不到使用此方法插入的方法.查看文档，似乎没有插入的方法.

I have managed to write a new table using the DBI package, but I can't find a way to insert into using this method. Looking at the documentation, there doesn't seem to be a way of inserting.

由于有1000多个数据行，使用RODBC包中的sqlQuery似乎也不可行.

As there are more than 1000 rows of data, using sqlQuery from the RODBC package also seems unfeasable.

有人可以建议一种可行的方法，将数据帧中的大量数据插入到现有的SQL表中吗?

Can anybody suggest a working method for inserting large amounts of data from a dataframe into an existing SQL table?

推荐答案

我使用R和PostGreSQL以及r-postgres特定的驱动程序也有类似的需求.我认为SQLServer可能存在类似的问题.我发现最好的解决方案是使用dbWriteTable或基础函数之一写入数据库中的临时表，以从流中写入以加载非常大的表(例如，对于Postgres，postgresqlCopyInDataframe).后者通常需要在定义和对齐SQL数据类型和R类类型方面进行更多工作，以确保编写，而dbWriteTable往往更容易一些.一旦写入临时表，就可以发出一条SQL语句，就像在数据库环境中一样，将其插入到表中.下面是使用高级DBI库数据库调用的示例:

I've had similar needs using R and PostGreSQL using the r-postgres-specific drivers. I imagine similar issues may exist with SQLServer. The best solution I found was to write to a temporary table in the database using either dbWriteTable or one of the underlying functions to write from a stream to load very large tables (for Postgres, postgresqlCopyInDataframe, for example). The latter usually requires more work in terms of defining and aligning SQL data types and R class types to ensure writing, wheres dbWriteTable tends to be a bit easier. Once written to a temporary table, to then issue an SQL statement to insert into your table as you would within the database environment. Below is an example using high-level DBI library database calls:

  dbExecute(conn,"start transaction;")
  dbExecute(conn,"drop table if exists myTempTable")
  dbWriteTable(conn,"myTempTable",df)
  dbExecute(conn,"insert into myRealTable(a,b,c) select a,b,c from myTempTable")
  dbExecute(conn,"drop table if exists myTempTable")
  dbExecute(conn,"commit;")

这篇关于如何将R数据帧插入SQL Server中的现有表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！