You can use the power of knitr to fill the content of a chunk programmatically thanks to the code chunk option. That means you can read you sql from a file to the chunk, then it is executed as any other SQL chunk.
Here is a reproducible example to show what I mean. I think it covers what you are looking for. Please tell me otherwise.
You can put the lines below in a Rmd file and render it.
---
title: "Use external SQL file for SQL chunks"
output: html_document
---
In order to provide a reproducible example, we use dplyr and dbplyr with `tbl_memdb` to load a table into a memory db.
```{r}
library(dplyr)
library(dbplyr)
iris_db <- tbl_memdb(iris)
# we isolate the connection object
con <- src_memdb()$con
```
We check that it works with SQL chunk to filter data into an R object
```{sql, connection=con, output.var="setosa"}
SELECT * FROM iris WHERE Species == "setosa"
```
The object `setosa` exists
```{r}
glimpse(setosa)
```
Know, let see how to load the SQL command from a file to a chunk then execute to save in a R object. We can do it through the power of knitr.
For the demo, we write some SQL in a script `versicolor.sql` using the `cat` engine that can write the content of a chunk to file.
```{cat, engine.opts = list(file = "versicolor.sql", lang = "sql")}
SELECT * FROM iris WHERE Species == "versicolor"
```
and we check that the file exists with some content
```{r}
readLines("versicolor.sql")
```
Now we can use this script to fill the content of the chunk and execute it like any sql chunk. We use the knitr chunk option `code` that allow to fill the content of a chunk programmatically.
```{sql, connection = con, code=readLines("versicolor.sql"), output.var="versicolor"}
```
The chunk is executed as any other SQL chunk. We check that versicolor data exists
```{r}
glimpse(versicolor)
```