CODEWITHSUNDEEP
sqlrsql-serverodbc
Ed Cunningham
Ed Cunningham

Reputation: 179

How to insert R dataframe into existing table in 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.

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?

Upvotes: 1

Views: 5785

Answers (1)

Soren
Soren

Reputation: 2445

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

Upvotes: 2

Related Questions