Directly Creating Tables on a Server

[omario]

I am working with the R programming language. Suppose I run the following code:

library(RODBC)
library(sqldf)

con = odbcConnect("some name", uid = "some id", pwd = "abc")

sample_query = sqlQuery(con, "select distinct * from table_a a 
inner join table_b b
on (a.date_1 between b.date_2 and b.date_3 and a.id1 = b.id1) or a.id2 = b.id2)

view(sample_query)

My Question: Is there a way to directly place "sample_query" on to the server specified in the "con" statement? Currently, "sample_query" is being created within the global environment in R studio - but is there a way to place "sample_query" on the server (i.e. the same place where "table_a" and "table_b" are located)?

Thanks!

[nutterb]

I'm not 100% confident I understand the goal--it sounds to me that you want to make the result of your query something that is persistently available on your server without having to execute the query from R each time you need the data.

If I am correct, what you likely want to do is create a View. Strictly speaking, yes you could do this from R, but this opens a lot of questions about user permissions, database administration, etc. You may want to reach out to your DBAs to see if they are willing/able to support your request.

[andresrcs]

Just a remark, a view might execute a little faster if it is frequently queried because of data been cached but it would need to execute anyways, if you want to cache the content regardless of how frequently is query and the query planner, you want a materialized view.

I think the intention of @omario is naively creating a new table in the database which kinda would have the same effect but it is much less maintainable and messy.