Can I set a default value for field.types when using dbWriteTable?

Question

I'm trying to write a dataframe to SQL Server using the following line

dbWriteTable(conn,"r_test_upload",df, field.types = c(longnotes ="varchar(max)"))

By default it's trying to upload all my fields as varchar(255), which is too short. I can manually specify the columns but I don't want to do that. I want it to assume varchar(max) unless I tell it otherwise.

I also tried

dbWriteTable(con,"r_test_upload2",result_csv, field.types = c(.default ="varchar(max)"))

but it assumed that was a field name and failed with the following:

Error: Columns in field.types must be in the input, missing columns: - '.default'

I'll also take any other package suggestions.

Answer 1

The dbWriteTable function is implemented individually in each driver package (e.g., RSQLite, odbc), so the answer technically depends on that ... but I think the end-result will be the same: you'll need to use a wrapper function.

Looking at the odbc package, we find that dbWriteTable (really, odbc_write_table) only uses field.types if a new table must be created, for which it calls sqlCreateTable. That calls createFields, which in this package appears to have strict requirements of names.

To do what you want, you'll probably need a wrapper function:

my_dbWriteTable <- function(conn, name, value, ..., field.types = NULL) {
  cl <- match.call(expand.dots = TRUE)
  cl[[1]] <- substitute(dbWriteTable)
  if (!is.null(field.types) && ".default" %in% names(field.types)) {
    othernames <- setdiff(colnames(value), names(field.types))
    cl$field.types <- c(
      field.types[ setdiff(names(field.types), ".default") ],
      setNames(rep(field.types[".default"], length(othernames)), othernames)
    )
  }
  eval.parent(cl)
}

my_dbWriteTable(con, "quux", mtcars, field.types=c(cyl="integer", .default="varchar(max)"))
DBI::dbGetQuery(con, "select column_name, data_type from information_schema.columns where table_name='quux'")
#    column_name data_type
# 1    row_names   varchar
# 2          mpg   varchar
# 3          cyl       int
# 4         disp   varchar
# 5           hp   varchar
# 6         drat   varchar
# 7           wt   varchar
# 8         qsec   varchar
# 9           vs   varchar
# 10          am   varchar
# 11        gear   varchar
# 12        carb   varchar

(This approach assumes that some form of dbWriteTable is visible/available from the calling environment.)