sql temporary tables in rstudio notebook's sql chunks?

Question

I am trying to use temp tables in an sql codechunk in rstudio.

An example: When I select one table and return it into an r object things seem to be working:

```{sql  ,  output.var="x",  connection='db'    }
  SELECT count(*) n
    FROM origindb
```

When I try anything with temp tables it seems like the commands are running but returns an empty r data.frame

```{sql  ,  output.var="x",  connection='db'    }
  SELECT count(*) n
    INTO #whatever
    FROM origindb

  SELECT *
   FROM #whatever
```

My impression is that the Rstudio notebook sql chunks are just set to make one single query. So my temporary solution is to create the tables in a stored procedure in the database. Then I can get the results I want with something simple. I would prefer to have a bit more flexibility in the sql code chunks.

my db connection looks like this:

```{r,echo=F}
db <- DBI::dbConnect(odbc::odbc(),
                      driver = "SQL Server",
                      server = 'sql',
                      database = 'databasename')
```

I think the issue is tied to DBI treating each query as a separate transaction and thus generating a new spid for each query. Since temp tables can't exist across spids it doesn't work. I am interested to see if anyone has a solution for this that doesn't involve creating an R object because I'm trying to do the same thing and want to create a training document for SQL Server users from the R Notebook. — ansek, Aug 15 '17 at 22:20

score 1 · Answer 1 · edited Aug 16 '17 at 18:53

I accomplished my goal using CTEs. As long as you define your CTEs in the order that they will be used it works. It is just like using temp tables with one big exception. The CTEs are gone after the query finishes where temp tables exist until you spid is kill (typically via a disconnect).

WITH CTE_WHATEVER AS (
                       SELECT COUNT(*) n 
                       FROM origindb
                      ) 
      SELECT * 
      FROM CTE_WHATEVER

You can also do this for multiple temp table examples

WITH CTE1 AS (
              SELECT 
                  STATE
                 ,COUNTY
                 ,COUNT(*) n 
              FROM origindb 
              GROUP BY 
                 STATE
                 ,COUNTY
              ),
     CTE2 AS (
              SELECT 
                 STATE
                 ,AVG(n)
                 ,COUNTY_AVG
              FROM CTE1 
              GROUP BY 
                 STATE
              )
SELECT * 
FROM CTE2 
WHERE COUNTY_AVG > 1000000

Sorry for the formatting. I couldn't figure out how to get the carriage returns to work in the code block.

I hope this helps.

score 1 · Accepted Answer · answered Feb 28 '18 at 17:22

1

Like this question, it will work if you put

set nocount on

at the top of your chunk. R seems to get confused when it's handed back the rowcount for the temp table.

answered Feb 28 '18 at 17:22

Nick

26
2

score -1 · Answer 3 · answered Dec 31 '19 at 10:11

-1

You could manage a transaction within the SQL chunk defining a BEGIN and COMMIT clauses. For example:

BEGIN ;
CREATE TABLE foo (id varchar) ;
COMMENT ON TABLE foo IS 'Foo';
COMMIT ;

answered Dec 31 '19 at 10:11

alazarlo

61
2

sql temporary tables in rstudio notebook's sql chunks?

3 Answers3