Ms-Sql Bulk Insert with Rodbc

using rodbcext with character columns

I have had good luck using parameterized queries, dataframes, and some string formatting. Here's the function I use, made generic and with explicit namespaces for function clarity:

library(RODBCext)  # Also loads RODBC as dependency
Connection <- RODBC::odbcConnect('DSN')  # I am assuming you can connect via RODBC

BulkUpload <- function(new_data) {

  # Get the column names for the new data to add
  columns <- colnames(new_data)

  # Get the valid column names from the SQL target table 
  sql_columns <- RODBC::sqlColumns(Connection, "target_table")$COLUMN_NAME

  # Check to make sure all the columns in the dataframe are valid target table columns
  if(sum(columns %in% sql_columns) != length(columns)){
    stop("Cannot complete upload-- One or more columns doesn't exist in target table")
  }

  # Generate the query dynamically based upon the column names and number of columns
  column_query <- paste(columns, collapse = ",")
  values_query <- paste(rep("?", length(columns)), collapse = ",")
  NewDataQuery <- sprintf("INSERT INTO target_table (%s) VALUES (%s)", column_query, values_query)

  # Make the parameterized query call, given there is no information to add
  ifelse(nrow(new_data) == 0, stop("No new data to add"),
         RODBCext::sqlExecute(Connection, NewDataQuery, new_data))
}

This is nice because it will only insert data into the columns you have specified in your dataframe column names. Keep in mind, you'll need to make sure your dataframe includes columns for any data fields that are required in your database as not null and don't have default values.

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

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).

What is the fastest way to load huge .Rdata files (R dataframes) into SQL Server?

I would export my data in CSV format and use BULK INSERT. If you really want (or have) to use sqlSave, then, the only options that come to my mind are:

1. be sure to set fast=TRUE in sqlSave
2. set auto commit off before running sqlSave: odbcSetAutoCommit(dbhandle, autoCommit = FALSE) and commit after sqlSave odbcEndTran(dbhandle, commit = TRUE)

But, in my opinion, the real solution is BULK INSERT.

SQL Server RODBC Connection

library(RODBC)
dbhandle <- odbcDriverConnect('driver={SQL Server};server=mysqlhost;database=mydbname;trusted_connection=true')
res <- sqlQuery(dbhandle, 'select * from information_schema.tables')

---

Related Topics