Export Data Frame to SQL Server Using Rodbc Package

How to quickly export data from R to SQL Server

By writing the data to a CSV locally and then using a BULK INSERT (not readily available as a prebuilt function akin to sqlSave), the data can be written to the MS SQL Server very quickly.

toSQL = data.frame(...);
write.table(toSQL,"C:\\export\\filename.txt",quote=FALSE,sep=",",row.names=FALSE,col.names=FALSE,append=FALSE);
    sqlQuery(channel,"BULK
                INSERT Yada.dbo.yada
                FROM '\\\\<server-that-SQL-server-can-see>\\export\\filename.txt'
                WITH
                (
                FIELDTERMINATOR = ',',
                ROWTERMINATOR = '\\n'
                )");

SQL Server must have permission to access the network folder holding the CSV file, or else this process will not work. While it takes some setup with various permissions (the network folder and BULK ADMIN privileges, the reward in speed is infinitely more valuable).

Insert R dataframe into SQL (RODBC) - error table not found

To avoid the error, you could specify the database in the connection string:

Driver=ODBC Driver 17 for SQL Server; Server = someserveraddress; database = some_db; Uid = user_login; Pwd = some_password

and avoid using brackets:

sqlSave(myconn, mydf, tablename = 'some_schema.my_table',  append = F, rownames = F,  verbose=TRUE)

How to insert a dataframe into a SQL Server table?

[edited] Perhaps pasting the names(df) would solve the scaling problem:

   values <- paste( " df[  , c(", 
                     paste( names(df),collapse=",") ,
                                   ")] ", collapse="" ) 
      values
      #[1] " df[  , c( a,b,c )] "

You say your code is "working".. I would also have thought one would use sqlSave rather than sqlQuery if one wanted to "upload".

I would have guessed this would be more likely to do what you described:

 sqlSave(con, df, tablename = "MyTable")

Insert R DataFrame into SQL Server Table

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

---

Related Topics