R: Directly Creating Tables on a Server-CodePudding

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!

CodePudding user response：

so you can make a store procedure in your RDBMS with your query like so(pseducode as it can be slightlu different syntax in different databse engines) :

create proc procname 
as 
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)
end

then :

library(RODBC)
library(sqldf)

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

sample_query = sqlQuery(con, "exec procname")

view(sample_query)

in some rdbms, command to call a proc is like call procname

CodePudding user response：

A SQL "VIEW" tends to be fairly compatible across DMBSes:

CREATE VIEW viewname
AS
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)

(Often it's considered a good practice to be explicit about columns being returned, i.e., not using *, though this can seen as preference to many.)

With this, treat viewname as if it were a table, with or without a WHERE clause.

SELECT * from viewname