Modify an existing one and copy to a new table in SQLServer using dplyr without using

Question

I found a question of a similar nature , since the author switched to using other tools for processing data, I would like to continue his question.

In gist, I want to modify an existing table in SQLServer and then insert a new table in SQLServer, specifying two tables in the database. I am currently doing something like this:

copy_to(channel,iris,'##iris',temporary=FALSE)

iris.st01 <- tbl(channel,'##iris') %>%
  mutate(`Sepal.Length.Sq`=`Sepal.Length`^2) %>%
  collect

copy_to(channel,iris.st01,'##iris_st01',temporary=FALSE)

A little “less beautiful” to copy data from the server and then paste it again, given that all changes can be made on the server. I wonder if he has any better ways to do this?

The answer to my previous question suggested using the package DBIis wondering if this package can do a similar trick.

Thank!

Update 01

I followed @Moody_Mudskipper's suggestion and modified my script as follows:

copy_to(channel,iris,'##iris',temporary=FALSE)

iris.st01 <- tbl(channel,'##iris') %>%
  mutate(`Sepal.Length.Sq`=`Sepal.Length`^2) %>%
  collect

# copy_to(channel,iris.st01,'##iris_st01',temporary=FALSE)
db_drop_table(channel,'##iris_st01')
DBI::dbSendQuery(channel,'SELECT "Sepal.Length", "Sepal.Width", "Petal.Length", "Petal.Width", "Species", POWER("Sepal.Length", 2.0) AS "Sepal.Length.Sq" INTO ##iris_st01 FROM ##iris')

It works, although you can see what SELECT "Sepal.Length", "Sepal.Width", "Petal.Length", "Petal.Width", "Species", POWER("Sepal.Length", 2.0) AS "Sepal.Length.Sq" FROM "##iris"comes from:

> iris.st01 <- tbl(channel,'##iris') %>%
+     mutate(`Sepal.Length.Sq`=`Sepal.Length`^2) %>% show_query
<SQL>
SELECT "Sepal.Length", "Sepal.Width", "Petal.Length", "Petal.Width", "Species", POWER("Sepal.Length", 2.0) AS "Sepal.Length.Sq"
FROM "##iris"

I want to use show_queryit to show the SQL query and to “insert” it into the database through DBI::dbSendQuery, it seems that it does not work for me when the query becomes very long (for example, with several joins).

Any clues here? Thank!