Pass R variable to RODBC's sqlQuery with multiple entries?

Question

I'm in the process of learning R, to wave SAS goodbye, I'm still new to this and I somehow have difficulties finding exactly what I'm looking for.

But for this specific case, I read: Pass R variable to RODBC's sqlQuery? and made it work for myself, as long as I'm only inserting one variable in the destination table.

Here is my code:

library(RODBC)
channel <- odbcConnect("test")
b <- sqlQuery(channel,
  "select top 1 Noinscr
   FROM table
   where PrixVente > 100
   order by datevente desc")

sqlQuery(channel,
   paste("insert into TestTable (UniqueID) Values (",b,")", sep = "")

When I replace the top 1 by any other number, let's say top 2, and run the exact same code, I get the following errors:

[1] "42000 195 [Microsoft][SQL Server Native Client 10.0][SQL Server]
    'c' is not a recognized built-in function name."      
[2] "[RODBC] ERROR: Could not SQLExecDirect 
    'insert into TestTable  (UniqueID) Values (c(8535735, 8449336))'"

I understand that it is because there is an extra c that is generated, I assume for column when I give the command: paste(b).

So how can I get "8535735, 8449336" instead of "c(8535735, 8449336)" when using paste(b)? Or is there another way to do this?

Answer 1

Look into the collapse argument in the paste() documentation. Try replacing b with paste(b, collapse = ", "), as shown below.

Edit As Joshua points out, sqlQuery returns a data.frame, not a vector. So, instead of paste(b, collapse = ", "), you could use paste(b[[1]], collapse = ", ").

library(RODBC)
channel <- odbcConnect("test")
b <- sqlQuery(channel,
  "select top 1 Noinscr
   FROM table
   where PrixVente > 100
   order by datevente desc")

sqlQuery(channel,
   ## note paste(b[[1]], collapse = ", ") in line below
   paste("insert into TestTable (UniqueID) Values (", paste(b[[1]], collapse = ", "),")", sep = "")

Answer 2

Assuming b looks like this:

b <- data.frame(Noinscr=c("8535735", "8449336"))

Then you only need a couple steps:

# in case Noinscr is a factor
b$Noinscr <- as.character(b$Noinscr)
# convert the vector into a single string
# NOTE that I subset to get the vector, since b is a data.frame
B <- paste(b$Noinscr, collapse=",")
# create your query
paste("insert into TestTable (UniqueID) Values (",B,")", sep="")
# [1] "insert into TestTable (UniqueID) Values (8535735,8449336)"

You got odd results because sqlQuery returns a data.frame, not a vector. As you learned, using paste on a data.frame (or any list) can provide weird results because paste must return a character vector.