Saving dbplyr query (tbl_sql object) to MySQL without saving data locally

Question

This question expands on this question

Here, I'm using the custom function created by @Simon.S.A. shown in the answer to this question. I'm attempting to save a tbl_sql object in R to MySQL as a new table without first saving it locally. Here, the database and schema in my MySQL are named "test." The tbl_sql object in R is my_data, and I want to save this is a new table in MySQL labeled "car_data".

library(DBI)
library(tidyverse)
library(dbplyr)


#establish connection and import data from MySQL

con <- DBI::dbConnect(RMariaDB::MariaDB(),
                      dbname = "test",
                      host = "127.0.0.1",
                      user = "user",
                      password = "password")

my_data <- tbl(con, "mtcars")
my_data <- my_data %>% filter(mpg >= 22)

# write function to save tbl_sql as a new table in SQL

  write_to_database <- function(input_tbl, db, schema, tbl_name){
    # connection
    tbl_connection <- input_tbl$src$con
    
    # SQL query
    sql_query <- glue::glue(
      "SELECT *\n",
      "INTO {db}.{schema}.{tbl_name}\n",
      "FROM (\n",
      dbplyr::sql_render(input_tbl),
      "\n) AS sub_query"
    )
    
    result <- dbExecute(tbl_connection, as.character(sql_query))
  }


# execute function

write_to_database(my_data, "test", "test", "car_data")

After running final line, I get the following error. I'm not sure how I can fix this.

Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.test.car_data
FROM (
SELECT *
FROM `mtcars`
WHERE (`mpg` >= 22.0)
) AS sub_quer' at line 2 [1064]
12.
stop(structure(list(message = "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.test.car_data\nFROM (\nSELECT *\nFROM `mtcars`\nWHERE (`mpg` >= 22.0)\n) AS sub_quer' at line 2 [1064]", 
call = NULL, cppstack = NULL), class = c("Rcpp::exception", 
"C++Error", "error", "condition")))
11.
result_create(conn@ptr, statement, is_statement)
10.
initialize(value, ...)
9.
initialize(value, ...)
8.
new("MariaDBResult", sql = statement, ptr = result_create(conn@ptr, 
statement, is_statement), bigint = conn@bigint, conn = conn)
7.
dbSend(conn, statement, params, is_statement = TRUE)
6.
.local(conn, statement, ...)
5.
dbSendStatement(conn, statement, ...)
4.
dbSendStatement(conn, statement, ...)
3.
dbExecute(tbl_connection, as.character(sql_query))
2.
dbExecute(tbl_connection, as.character(sql_query))
1.
write_to_database(my_data, "test", "test", "car_data")

Answer 1

Creating a table with INTO command is an SQL Server (even MS Access) specific syntax and not supported in MySQL. Instead, consider the counterpart statement: CREATE TABLE...SELECT. Also, schema differs between RDBMS's. For MySQL, database is synonymous to schema.

Therefore, consider adjusted version of SQL build:

sql_query <- glue::glue(
      "CREATE TABLE {db}.{tbl_name}\n AS \n",
      "SELECT * \n",
      "FROM (\n",
      dbplyr::sql_render(input_tbl),
      "\n) AS sub_query"
    )