Temporary tables in MSSQL

[Donnie Musgrove]

I've arrived here via the Issues submission page of the dplyr Github repo. With respect to creating temporary tables in MSSQL via dplyr/dbplyr, I am not sure if there is a bug or if I am using the wrong code. Here are the details. I can successfully connect to my MSSQL database via:

con <- DBI::dbConnect(odbc::odbc(), "MyMSSQLDatabase")

I can query tables, join tables, and collect the results. Fine, now I want to create a temporary table. I use:

q1 <- tbl(con, "TableInDB") %>%

  select(ColumnOfInterest) %>%

  compute(name="#TemporaryTableInDB")

I receive the following error:

Error: <SQL> 'CREATE TEMPORARY TABLE "#TemporaryTableInDB" AS SELECT "ColumnOfInterest" AS "ColumnOfInterest"

FROM (SELECT "ColumnOfInterest" AS "ColumnOfInterest"

FROM "TableInDB") "nalrdixbop"'

  nanodbc/nanodbc.cpp:1587: 42000: [Microsoft][ODBC SQL Server Driver][SQL Server]Unknown object type 'TEMPORARY' used in a CREATE, DROP, or ALTER statement. 

I can recognize that the generated SQL code is incorrect for creating temporary tables in MSSQL. So, is this a currently unsupported feature or am I doing this incorrectly?

Thanks,

Donnie

[ed...@rstudio.com]

Hi Donnie,

Would you mind opening an Issue in GitHub to take a look at the compute verb for MS SQL database connections?  At this time, you can use copy_to(sc, df, "##df", temporary = FALSE).  Currently there's no support for local temporary tables, only global.  I suspect that will be the best alternative for compute.

[Donnie Musgrove]

I raised the issue on Github but I wanted clarification from you here. What do you mean by "Currently there's no support for local temporary tables, only global"? I'm not sure how the terms local and global are used in this context, but my understanding of my code is that a temporary table should be created on the MSSQL server by copying my ColumnOfInterest from a table on the MSSQL server into that temporary table. Is that correct?

[ed...@rstudio.com]

Hi Donnie, here's some info about Global and Local temp tables in SQL: http://sqlmag.com/t-sql/temporary-tables-local-vs-global