Pass R Vector to Sql query
The new dbplyr
package has the best answer for this. It allows any R object to be used, and automatically converts it to SQL
https://db.rstudio.com/dplyr/
How to paste an R vector to sql query?
id <- c("a1", "s44", "qq11", "f5")
First you want to concatenate the elements of id
with commas, so that it can be inserted into your SQL statement.
sql_list_string <- paste(id, collapse = "', '")
sql_list_string
#> [1] "a1', 's44', 'qq11', 'f5"
Then, paste()
it into your SQL statement.
query <-
paste0("SELECT * from my.table WHERE col1 in ('", sql_list_string, "')")
query
#> [1] "SELECT * from my.table WHERE col1 in ('a1', 's44', 'qq11', 'f5')"
You can now use query in your call to dbGetQuery()
like this: dbGetQuery(con, query)
Pass R variable to RODBC's sqlQuery with multiple entries?
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 = "")
R RODBC putting list of numbers into an IN() statement
You're not paste
ing your query together correctly.
If you run the paste
statement in isolation, you'll see that you get a vector of length 5000, so sqlQuery
is only executing the first one of those, corresponding to the first element in samRowNum
.
What you want to do is something more like this:
paste("select * FROM db where row_id in (",
paste(sampRowNum,collapse = ","),")", sep="")
Just as an added note (and since I've had to do stuff like this a lot...) constructing sql queries with an IN
clause with strings is a bit more of a nuisance, since you have to tack on all the single quotes:
vec <- letters[1:5]
paste("SELECT * FROM db WHERE col IN ('",
paste(vec,collapse = "','"),"')",sep = "")
[1] "SELECT * FROM db WHERE col IN ('a','b','c','d','e')"
If you do this a lot, you'll end up writing a little function that does that pasting of character vectors for you.
As always, this kind of SQL string manipulation is Not Good if you are dealing with user inputs (e.g. in a web app), due to SQL injection attacks. In my particular situation this isn't much of a concern, but in general people will prefer parametrized queries if you don't have much control over the input values.
Passing a vector of characters into another string in R
If you are going to generate a lot of SQL, I recommend that you look into glue_sql()
, because it is especially designed for this use case and has a lot of nice features:
library(glue)
## set up example db:
con <- DBI::dbConnect(RSQLite::SQLite(), ":memory:")
DBI::dbWriteTable(con, "chickwts", chickwts)
tbl <- "chickwts"
feed <- list("soybean", "sunflower")
feed <- glue_sql_collapse(single_quote(feed), sep = ", ")
glue_sql("
SELECT * FROM {`tbl`}
WHERE feed IN ({feed})
", .con = con)
#> <SQL> SELECT * FROM `chickwts`
#> WHERE feed IN ('soybean', 'sunflower')
Created on 2022-10-21 with reprex v2.0.2
Related Topics
How to Get the Last Month Data and Month to Date Data
Solution for Speeding Up a Slow Select Distinct Query in Postgres
How to Get the Sum of All Column Values in the Last Row of a Resultset
Maximum Length of an SQL Query
Cycle Detection with Recursive Subquery Factoring
Postgres: Select All Row with Count of a Field Greater Than 1
Checking If a Given Date Fits Between a Range of Dates
Does Introducing Foreign Keys to MySQL Reduce Performance
Retrieving SQL Queries from Active-Record Queries in Rails 3
Alter Table to Modify Default Value of Column
Rails Pg::Undefinedtable: Error: Missing From-Clause Entry for Table