For inserting SQL NULLs for variables in R that are NA

R-NAs-to-SQL-NULLs.R

require(RODBC)

con <- odbcConnect("dsn")

clean <- function(x) {
  if (is.character(x)) {
    out <- paste0("'", x, "'")
  } else if (is.na(x)) {
    out <- "NULL"
  } else {
    out <- x
  }
  if (out == "'NA'") {
    out <- "NULL"
  }
  return(out)
}


for (i in 1:2) {
  val1 <- clean(x[i, 1])
  val2 <- clean(x[i, 2])
  val3 <- clean(x[i, 3])
  query <-
    paste(
      "INSERT INTO db.schema.table VALUES(",
      val1,
      ",",
      val2,
      ",",
      val3,
      ")"
    )
  sqlQuery(con, query, as.is = T)
}

odbcClose(con)