Refer to existing object inside sqlQuery

General
,
#1

Hi,

I'm a beginner in RStudio, but been looking for the answer for quite a while already.

I'm using RODBC sqlQuery to retrieve data from database with ODBC connection.
However, inside sqlQuery I would like to refer to an existing object (table) in my RStudio project.

Lets say I've loaded data from xlsx file onto RStudio into "table1". I want to join some data to this table using sqlQuery and ODBC from database. How do I go about this? If I just refer to this table as "from table1" inside the SQL query, it says "table or view does not exist".

It feels to me there should be a way to do this. I can't just load the whole table B from database and then join them with R, because the table is huge.

Ray

#2

If you have write permissions you can copy table1 temporarily to the RDBMS so you can join them.

#3

Hi. I don’t unfortunately.
Anyhow I want to believe there must be a simpler solution. This action seems very basic to me.

In WPS (world programming service), for instance, you can specify the source when calling for sql. Like to get data from db I would add a prefix to table name with the db name. Likewise, to get data from temporary tables loaded into wps I would add prefix “work”.

#4

The thing is that the sql query you write in R is executed on the RDBMS not in your R environment and to manipulate both tables at the same time they both need to be on the same environment. There are some implementations that make this transparent to the user by copying the tables in the background either to the RDBMS or locally (like dbplyr) but I'm afraid you have to choose one of the two approaches.

Even with your example a temporary copy is made on the RDBMS environment.

#5

You can use local() to refer to objects in the working environment when interacting with databases in R.

#6

When using dbplyr to perform a join operation, if you set the copy argument to false, I think it embeds the table on the generated sql query itself, that way you don't need to have write permissions. I think this could be a good option as long as you can define the table within the sql query programmatically (like with dbplyr).

Even if I'm wrong with my assumption, there is a way to explicitly tell dbplyr to copy the table in-line.