How to correctly use sqlAppendTable?

Question

Short summary

I'm trying to insert data from an R data.frame into a table on an SQLServer database using the DBI package. After reading the information about sqlAppendTable I was hoping this function could help me generate the necessary SQL statement. However, it seems this function does not put strings around character variables, thus generating an error when trying to execute it. Am I using it correctly? Should I be using this function for this purpose at all? And if not, can you recommend another approach?

My code

library(odbc)
library(DBI)

con <- dbConnect(
    odbc::odbc(),
    dsn      = myDsn,
    UID      = myLogin,
    PWD      = myPwd,
    Port     = 1433,
    encoding = "latin1"
  )

insertStatement <- sqlAppendTable(
    con,
    "DBtable",
    myDataFrame,
    row.names = FALSE
  )

dbExecute(
  con,
  insertStatement
)

dbDisconnect(con)

The database table "DBtable" has 3 columns, each of which have type varchar. The data.frame "myDataFrame" also has 3 columns of type character with the same names in the same order.

The problem

sqlAppendTable generates an SQL statement where the character variables are not quoted, i.e. an output of the form:

<SQL> INSERT INTO "DBtable"
  ("col1", "col2", "col3")
VALUES
  (Value one one, Value one two, Value one three),
  (Value two one, Value two two, Value two three),
  etc.

When this output is used in a dbExecute statement, it generates an error because the values are not quoted, i.e. Value one one, ... instead of 'Value one one', ....

My questions

• Is there a way to get this function to put quotes around character variables? And if so, how?
• Can I use this function for this purpose at all? (The info states that it is "mostly useful for backend implementers", whatever that means.)
• If I cannot, is there another function I could use? I would prefer to avoid creating custom made statements using paste (or similar functions), as this tedious, error prone, and not easily replicated for different tables.

Answer 1

I had the same issues but then created a small helper function that takes a data.frame as an input and quotes every value in it:

sQuote.df <- function(df) {
    for (c in 1:ncol(df)) df[,c] <- sQuote(gsub("'", "`", df[,c]))
    df
}

(note that the gsub function here us used to change potential single quotes in the data.frame to backward apostrophs)

Using this in sqlAppendTable like

sqlAppendTable(connection, "table", sQuote.df(df_to_insert), row.names=F)

made the function very handy and useful for me.