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

Question

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.

Answer 1

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 )