dmvianna dmvianna - 1 year ago 84
SQL Question

Providing lookup list from R vector as SQL table for RODBC lookup

I have a list of IDs in an R vector.

IDlist <- c(23, 232, 434, 35445)

I would like to write an RODBC sqlQuery with a clause stating something like

WHERE idname IN IDlist

Do I have to read the whole table and then merge it to the idList vector within R? Or how can I provide these values to the RODBC statement, so recover only the records I'm interested in?

Note: As the list is quite long, pasting individual values into the SQL statement, as in the answer below, won't do it.

Answer Source

You could always construct the statement using paste

IDlist <- c(23, 232, 434, 35445)
paste("WHERE idname IN (", paste(IDlist, collapse = ", "), ")")
#[1] "WHERE idname IN ( 23, 232, 434, 35445 )"

Clearly you would need to add more to this to construct your exact statement