Search code examples
rrodbc

using rodbcext with character columns

I need to do a bulk insert and am looking here:

MS-SQL Bulk Insert with RODBC

library(RODBCext)

connHandle <- odbcConnect("DBName", uid="user", pwd="password")
query <- "INSERT INTO MYTABLE (Col1, Col2, Col3, Col4, Col5, Col6, Col7) VALUES (?, ?, ?, ?, ?, ?, ?)"
sqlExecute(connHandle, query, df)

odbcClose(connHandle)

That example only has numeric columns inserted. I have numeric and character. Any idea how to add functionality for both numeric and character column inserts?

Solution

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