Insert R Data frame as parameter in SQL query WHERE clause

August 26, 2022

Trying to insert R data frame as parameter in SQL query where clause but I get a SQL error.

This is my code:

df <- data.frame(X=c(12691683,12693928)) %>% paste0(collapse = ",")

dbGetQuery(con2,"
  SELECT * FROM ORDERS WHERE ORDER_ID IN (", df, ")
")

>Solution :

With glue::glue_sql(), you could input multiple values for use in SQL IN statements by putting * at the end of the value and the values will be collapsed and quoted appropriately.

df <- data.frame(X = c(12691683, 12693928))

glue::glue_sql(
  "SELECT * FROM ORDERS WHERE ORDER_ID IN ({df$X*})",
)

# <SQL> SELECT * FROM ORDERS WHERE ORDER_ID IN (12691683, 12693928)

In base you could also use sprintf() to create the SQL query:

sprintf("SELECT * FROM ORDERS WHERE ORDER_ID IN (%s)",
        paste(df$X, collapse = ','))

# [1] "SELECT * FROM ORDERS WHERE ORDER_ID IN (12691683,12693928)"