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 ?

I am not so sure. But maybe , mytable <- noquote("name_of_table_on_sql_server") — maydin
– maydin, Commented Jul 24, 2019 at 18:13
No, this gets same error as glue() or as.name(): Error in interpolate_from_env(conn, sql) : Object(s) not found: " name_of_table_on_sql_servers " Failed to execute SQL chunk (note the blanks around the table name ?) — Jrm_FRL
– Jrm_FRL, Commented Jul 24, 2019 at 19:35

D. Woods · Accepted Answer · 2019-11-15 19:46:01Z

6

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.

answered Nov 15, 2019 at 19:46

D. Woods

3,0933 gold badges31 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jrm_FRL Over a year ago

Thank you ! This solution works perfectly. I would never have had the idea of looking for this sql_glue function.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related