2

I have run a SQL statements and stored the results in a local data frame called "test", `{sql connection=Prod, output.var="test"}.

Now, I need to access "test" the local data frame in another R SQL chunk, is that possible?


     SELECT COUNT(*) AS 'RecordCount'
         , EMPD
         , Department 

     FROM "test"
     GROUP BY EMPD
         , Department
Gregor Thomas
  • 136,190
  • 20
  • 167
  • 294
user3482393
  • 327
  • 4
  • 14

1 Answers1

0

Generally no. The SQL database server you've connected to doesn't know anything about your R session, so it can't process your R data.

The sqldf package lets you use SQL on R objects by creating a local database. It's a good solution if you're more comfortable using SQL than R, or if you want to do something that's easier in SQL than in R.

In most cases, the point of running a SQL query and storing the results in R is to use R, not SQL, for the next steps. Your example SQL code can be translated to dplyr like this:

library(dplyr)
count(test, EMPD, Department, name = "RecordCount")

If you need to reference the results in a new SQL query, for example to do a join with another table in the database, the best solution will depend on your use case, what flavor of SQL database you're using, and how big the results are. You may be able to use one big SQL query instead of two small ones, or perhaps write the intermediate results to a temporary table.

Gregor Thomas
  • 136,190
  • 20
  • 167
  • 294
  • I was trying to translate a large SQL Server statement where multiple "temp" tables were used, but could not get the data from the SQL chunks in RStudio to write to the SQL Server temp tables. This is why I was separating the queries and storing the results locally acting as temp tables. Some queries joins tables from SQL server and temp tables (in my cases stored locally) this is why I was interested in accessing the locally stored dataframes from the SQL chunk. Not sure why the INTO #temp statement seem to have no effect inside of the SQL chunk but works fine when run in SQL server. – user3482393 Dec 06 '21 at 22:07
  • That's all very relevant info that would be good to include in a new question. "Why do my `select into #temp statements` not work when connecting from R?" Searching for that issue [came up with this question](https://stackoverflow.com/q/4747768) that might help. Common table expressions may be a good workaround. – Gregor Thomas Dec 06 '21 at 22:09
  • Is it because `select into #temp` is a local-temporary table that is harvested as soon as the calling connection is closed? – r2evans Dec 07 '21 at 02:06
  • From like 10 years ago I remember a similar issue, and even using temp tables within a single statement failed. I think Chris Gheen's [answer here](https://stackoverflow.com/a/11423751/903061) might do it---it says RODBC by default will think the query is complete when the first temp table statement is executed, but gives an option for turning that off. – Gregor Thomas Dec 07 '21 at 03:18
  • That's an interesting premise. I have always assumed that the life of a temporary table was prescribed by the DBMS, not by the connection itself. That is, if for some reason `RODBC` or `DBI`/`odbc` hard-fail, then the temp table still goes away, despite the ODBC client's inability to properly clean up after itself. If `RODBC` is working around that, then ... that sounds like it could be intervening in temp-table operations, which could be difficult considering some require temp tables to be `#`-leading, others do not, so it's not always clear which is which. – r2evans Dec 16 '21 at 20:15