Pass R variable to RODBC sqlQuery with multiple records?

Question

I am involved in learning R to excite SAS bye, I am still new to this and it is somehow difficult for me to find what I am looking for.

But for this specific case, I read: pass R variable to RODBC sqlQuery? and made it work for me while I only insert one variable into 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 top 1 with any other number, say top 2 and run the 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 this is because there is an additional c generated that I accept for the 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 at the collapse argument in the paste() documentation. Try replacing b with paste(b, collapse = ", ") , as shown below.

Edit As Joshua points out, sqlQuery returns data.frame, not a vector. So, instead of paste(b, collapse = ", ") you can 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 need only a couple of 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 data.frame , not a vector. As you know, using paste in data.frame (or any list) can provide strange results, because paste must return a character vector.