How to insert R dataframe into existing table in SQL Server

Question

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.

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.

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

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

I'd recommend using library(odbc) and dbWriteTable(myCon, "myTable", myTable, append = TRUE) or a dbExecute(myCon, sprintf("INSERT INTO myTable VALUES (%s);", paste(myValues, collapse = "),("))) construct. Please see this related question and this useful comment on the topic. — ismirsehregal
– ismirsehregal, Commented Feb 14, 2019 at 15:11
Would this still be limited by maximum 1000 row insert limit in SQL Server? — Ed Cunningham
– Ed Cunningham, Commented Feb 14, 2019 at 15:28
Unfortunately, yes. In my case I wrote a loop which inserts the data in 1000 row batches. — ismirsehregal
– ismirsehregal, Commented Feb 14, 2019 at 15:43

Soren · Accepted Answer · 2019-02-14 15:09:16Z

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;")

Collectives™ on Stack Overflow

How to insert R dataframe into existing table in SQL Server

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related