1

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?

Ed Cunningham
  • 179
  • 1
  • 3
  • 17
  • I'd recommend using library([odbc](https://github.com/r-dbi/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](https://stackoverflow.com/questions/1402001/ms-sql-bulk-insert-with-rodbc/42489082#42489082) and this useful [comment](https://github.com/r-dbi/odbc/issues/34#issuecomment-296739145) on the topic. – ismirsehregal Feb 14 '19 at 15:11
  • Would this still be limited by maximum 1000 row insert limit in SQL Server? – Ed Cunningham Feb 14 '19 at 15:28
  • Unfortunately, yes. In my case I wrote a loop which inserts the data in 1000 row batches. – ismirsehregal Feb 14 '19 at 15:43

1 Answers1

2

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;")
Soren
  • 1,792
  • 1
  • 13
  • 16