Insert R DataFrame into SQL Server Table

Question

I have been stuck on this for a couple days now...I cant find what the issue could be. I am using R in Spotfire. I am trying to insert the results of a dataframe into a sql server 2014 table. This is an example of what I am using:

install.packages("ODBC")
install.packages("DBI")


library(DBI)
library(odbc)

con <- dbConnect(odbc(),
             Driver = "SQL Server",
             Server = "ServerName",
             Database = "DatabaseName",
             UID = "UserName",
             PWD = "Password")
dbWriteTable(conn = con, 
         name = "SQlServerTableDestinationName", 
         value = Datatable)

I get this error:

TIBCO Enterprise Runtime for R returned an error: 'Error in .loadNamespaceImpl(package, path, keep.source, partial) : error executing useDynLib for dynamic library 'rlang' from package 'rlang' loaded from P:/TERR/x86_64-pc-windows-library/4.2 : Error in library.dynam(chname = chname, package = package, lib.loc = ... : Foreign binary rlang could not be loaded'.

Thank you!

Alex Skorokhod · Accepted Answer · 2018-08-15 01:23:21Z

3

Please follow the TIBCO community solution suggested here: https://community.tibco.com/wiki/tibcor-enterprise-runtime-r-fast-writeback-sql-server-2016

In your case it would be something like below:

dbcon <- RODBC::odbcDriverConnect(connection_string )
RODBC::sqlSave(dbcon, dat =  dataf, "SQlServerTableDestinationName")

Please let me know if it helps

answered Aug 15, 2018 at 1:23

Alex Skorokhod

5401 gold badge5 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

yanci Over a year ago

I tried that as well and I got: TIBCO Enterprise Runtime for R returned an error: 'Error: cannot convert object to a data frame'. at Spotfire.Dxp.Data.DataFunctions.Executors.LocalFunctionClient.OnExecuting() at Spotfire.Dxp.Data.DataFunctions.Executors.AbstractFunctionClient.<RunFunction>d__0.MoveNext() at Spotfire.Dxp.Data.DataFunctions.Executors.SPlusFunctionExecutor.<ExecuteFunction>d__0.MoveNext() at Spotfire.Dxp.Data.DataFunctions.DataFunctionExecutorService.<ExecuteFunction>d__6.MoveNext() The link is for SQL Server 2016, I'm still using 2014

Alex Skorokhod Over a year ago

OK. 2 questions in this case: 1) can you please provide here the str(df) of the object you want to upload? I would like to see if anything prevents you from converting your object into data.frame using e.g. as.data.frame() 2) is there an option for you to store the file into a .csv format on the drive which will be visible to the SQL server? Not the most elegant way but you will be able to BULK INSERT directly into SQL Server from there...

ASH · Accepted Answer · 2018-08-15 19:48:09Z

2

Since insert INTO is limited to 1000 rows, you can dbBulkCopy from rsqlserver package.

dbBulkCopy is a DBI extension that interfaces the Microsoft SQL Server popular command-line utility named bcp to quickly bulk copying large files into table. For example:

url = "Server=localhost;Database=TEST_RSQLSERVER;Trusted_Connection=True;"
conn <- dbConnect('SqlServer',url=url)
## I assume the table already exist
dbBulkCopy(conn,name='T_BULKCOPY',value=df,overwrite=TRUE)
dbDisconnect(conn)

answered Aug 15, 2018 at 19:48

ASH

20.5k28 gold badges115 silver badges247 bronze badges

1 Comment

Alex Skorokhod Over a year ago

Are you sure it is compatible with TIBCO R? According to this doc: (docs.tibco.com/pub/enterprise-runtime-for-R/3.1.0/doc/pdf/…) RSQLServer package has issies under TIBCO.

yanci · Accepted Answer · 2018-08-16 12:57:44Z

1

I tried this and it finally worked

install.packages("RODBC")
library(RODBC)

dbcon <- RODBC::odbcDriverConnect('driver={SQL    Server};server=ServerName;database=DatabaseName;uid=UserName;pwd=Password' )
sqlSave(dbcon, DataFrame, "SQLServerName", verbose=TRUE, fast=TRUE, append=TRUE, rownames = FALSE)

answered Aug 16, 2018 at 12:57

yanci

1731 silver badge15 bronze badges

2 Comments

Alex Skorokhod Over a year ago

Hi mate, would mind accepting my suggestion below as correct in this case? It is exactly 1:1 with what you confirm as working :)

yanci Over a year ago

ah sorry about that, I forgot that you mentioned it! It gave me an error so I had forgot

Collectives™ on Stack Overflow

Insert R DataFrame into SQL Server Table

3 Answers 3

2 Comments

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related