Get the results sets of a parametrized query `rbind`ed *and* directly in the database using R's `DBI`

Using package:DBI, I need to:

1. run a parametrized query with different parameters (i.e. a vector of parameters);
2. get the results sets concatenated (i.e. rbinded as per R terminology or unioned as per SQL terminology);
3. and get the resulting table in the database for further manipulation.

dbBind()/dbGetquery() fullfils requirements 1 and 2, but I then need to write the resulting data frame to the database using dbWriteTable(), which is ineficient:

library(DBI)
con <- dbConnect(RSQLite::SQLite(), ":memory:")
dbWriteTable(con, "iris", iris)

res <- dbGetQuery(con,
                  "select * from iris where Species = ?",
                  params = list(c("setosa", "versicolor")))

dbWriteTable(con, "mytable", res)

Conversely, dbExecute() fulfils requirement 3, but I don't think it has the "rbind feature". Of course, this throw an error because the table would get overwritten:

dbExecute(con,
          "create table mytable as select * from iris where Species = ?",
          params = list(c("setosa", "versicolor")))

What is the most efficient/recommended way of doing so?

Notes:

• I am not the DBA and can only access the database through R.
• My example is too trivial and could be achieved in a single query. My use case really requires a parametrized query to be run multiple times with different parameters.
• I have to use Oracle, but I am interested in a solution even if it don't works with Oracle.

1) Create the table with the first parameter and then insert each of the others into it.

library(RSQLite)

con <- dbConnect(SQLite())
dbWriteTable(con, "iris", iris)

parms <- c("setosa", "versicolor")

dbExecute(con, "create table mytable as
  select * from iris where Species = ?",
  params = parms[1])
for (p in parms[-1]) {
  dbExecute(con, "insert into mytable
    select * from iris where Species = ?",
    params = p)
}

# check
res <- dbGetQuery(con, "select * from mytable")
str(res)

2) Alternately generate the text of an SQL statement to do it all. sqldf pulls in RSQLite and gsubfn which supplies fn$ that enables the text substitution.

library(sqldf)

con <- dbConnect(SQLite())
dbWriteTable(con, "iris", iris)

parms <- c("setosa", "versicolor")
parmString <- toString(sprintf("'%s'", parms))
fn$dbExecute(con, "create table mytable as
  select * from iris where Species in ($parmString)")

# check
res <- dbGetQuery(con, "select * from mytable")
str(res)

3) A variation of (2) is to insert the appropriate number of question marks.

library(sqldf)

con <- dbConnect(SQLite())
dbWriteTable(con, "iris", iris)

params <- list("setosa", "versicolor")
quesString <- toString(rep("?", length(params)))

fn$dbExecute(con, "create table mytable as
  select * from iris where Species in ($quesString)", params = params)

# check
res <- dbGetQuery(con, "select * from mytable")
str(res)

Based on @r2evans comment and @G.Grothendieck answer, instead of query/download/combine/upload, I used a parameterized query that inserts directly into a table.

First, I created the table with the appropriate columns to collect the results:

library(DBI)
con <- dbConnect(RSQLite::SQLite(), ":memory:")

create_table <-
"
CREATE TABLE
  warpbreaks2 (
    breaks real,
    wool text,
    tension text
);
"

dbExecute(con, create_table)

Then I executed an INSERT INTO step:

dbWriteTable(con, "warpbreaks", warpbreaks)

insert_into <-
"
INSERT INTO
  warpbreaks2
SELECT
  warpbreaks.breaks,
  warpbreaks.wool,
  warpbreaks.tension
FROM
  warpbreaks
WHERE
  tension = ?;
"

dbExecute(con, insert_into, params = list(c("L", "M")))

This is a dummy example for illustration purpose. It could be achieve more directly with e.g.:

direct_query <-
"
CREATE TABLE
  warpbreaks3 AS
SELECT
  *
FROM
  warpbreaks
WHERE
  tension IN ('L', 'M');
"

dbExecute(con, direct_query )