Rmarkdown - Use table name as variable in dynamic sql chunk?

Question

I need to execute an SQL-engine chunk in my Rmarkdown, where the table which is queried has a dynamic name, defined by R code. I know that linking variables to the current R-environment is doable by using ?, but this works only for strings and numerics, not for "objects".

Of course I could just run the SQL query with DBI::dbGetQuery() but this would imply building all my request (which is very long) as a string which is not comfortable (I have many chunks to run).

Basically what I would need is :

`` {r}
mytable <- "name_of_table_on_sql_server"
`` 

then

`` {sql} 
select * from ?mytable
`` 

This fails because the created query is select * from "name_of_table_on_sql_server" where SQL would need select * from name_of_table_on_sql_server (without quotes).

Using glue for defining mytable as mytable <- glue("name_of_table_on_sql_server") is not working neither.

Any idea ?

Answer 1

A slight variant on what you posted works for me (I don't have SQL Server so I tested with sqlite):

`` {r}
library(glue)
mytable <- glue_sql("name_of_table_on_sql_server")
`` 

then

`` {sql} 
select * from ?mytable;
`` 

My only real changes were to use the function glue_sql and add a semicolon (;) to the end of the SQL chunk.