3

I believe I should be able to do select * into #temptable from othertable (where #temptable does not previously exist), but it does not work. Assuming that othertable exists and has valid data, and that #sometemp does not exist,

# conn <- DBI::dbConnect(...)
DBI::dbExecute(conn, "select top 1 * into #sometemp from othertable")
# [1] 1
DBI::dbGetQuery(conn, "select * from #sometemp")
# Error: nanodbc/nanodbc.cpp:1655: 42000: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid object name '#sometemp'.  [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Statement(s) could not be prepared.

The non-temporary version works without error:

DBI::dbExecute(conn, "select top 1 * into sometemp from othertable")
# [1] 1
DBI::dbGetQuery(conn, "select * from sometemp")
### ... valid data ...

System info:

conn
# <OdbcConnection> myuser@otherdomain-DATA01
#   Database: dbname
#   Microsoft SQL Server Version: 13.00.5026
DBI::dbGetQuery(conn, "select @@version")
#                                                                                                                                                                                                                          
# 1 Microsoft SQL Server 2016 (SP2) (KB4052908) - 13.0.5026.0 (X64) \n\tMar 18 2018 09:11:49 \n\tCopyright (c) Microsoft Corporation\n\tStandard Edition (64-bit) on Windows Server 2016 Standard 10.0 <X64> (Build 14393: )\n

Tested on Win11 and Ubuntu. R-4.1.2, DBI-1.1.2, odbc-1.3.3.

I've seen some comments that suggest "select into ..." isn't for temporary tables, but I've also seen several tutorials demonstrate that it works (for them).

Back-story: this is for a generic accessor function for upserting data: I insert into a temp table, do the upsert, then remove the temp table. I can use a non-temp table, but I think there are valid reasons to use temps when justified, and I want to understand why this doesn't or shouldn't work as intended. Other than switching from temps, I could try to reconstitute the structure of the othertable programmatically, but that is prone to interpretative error with some column types. I can't just insert into a temp table since there are times when the data types are imperfectly mapped (such as when I should use nvarchar(max) and/or when a new column is indeterminant due to being all-NA).

Related links:

r2evans
  • 141,215
  • 6
  • 77
  • 149
  • _I've also seen several tutorials demonstrate that it works_ Show us. And beware of relying on posts that are more than a few years (over a decade!) old. The short answer is that using temp tables requires a thorough understanding of scope and how that determines when they get removed by the database engine. In turn you need to know how your data access layer handles connections when used for multiple statements. – SMor Feb 19 '22 at 19:29
  • **Links**: sure, edited into the question, I think 2021 is recent enough (and I'm *inferring* the credibility of sqlshack). Do you know of breaking changes to SQL Server over the last decade where they used to support something and reversed that support? **Scope**: do you mean `#` are local and `##` are "global local" (for the user)? I understand when they are closed, and that is unrelated when the connection is constant, uninterrupted. **Multiple statements**: R's `DBI`/`odbc` do not support multiple statements, unfortunately. Do you have any experience with R? – r2evans Feb 19 '22 at 19:51
  • 1
    I've found this inconsistent too. You can use `tbl(conn, sql("select top 1 * from othertable")) %>% compute()`, which will create a temp table on the database containing the first row of othertable. If you assign that call to an an object, say `tt`, you can work with it as usual. You can access the name of that object as well (`tt$ops$x$x`), which will look something like `#dbplyr_005` – langtang Feb 19 '22 at 20:06
  • 1
    I can get global temp tables to work this way: `DBI::dbExecute(mycon, "select top 5 * into ##mytemp from sometable")` followed by `DBI::dbGetQuery(mycon, "select * from ##mytemp")`. Does that help at all? – langtang Feb 19 '22 at 20:18

1 Answers1

4

There are few different approaches:

  1. Use the immediate arg in your DBI::dbExecute statement
DBI::dbExecute(conn, "select top 5 * into #local from sometable", immediate=TRUE)
DBI::dbGetQuery(conn, "select * from #local")
  1. Use a global temp table
DBI::dbExecute(conn, "select top 5 * into ##global from sometable")
DBI::dbGetQuery(conn, "select * from ##global")
  1. Use dplyr/dbplyr
tt = tbl(conn, sql("select top 5 * from sometable")) %>% compute()
tt

Also see here: https://github.com/r-dbi/odbc/issues/127

langtang
  • 22,248
  • 1
  • 12
  • 27
  • Thanks, I was in the middle of reading more about `immediate=` since I found that that can make my query work as intended. Thanks for making that suggestion! I'm surprised to see that `##`-temps *work* (*confirmed*, thank you), especially in light of the apparent need for `immediate=TRUE`, which I don't fully grok. (And though I looked, I missed `r-dbi/odbc#127`, thanks for the link.) – r2evans Feb 19 '22 at 20:34
  • I also don't understand why it works into a non-temporary table without the need for `immediate=TRUE`. Do you understand the difference? – r2evans Feb 19 '22 at 20:38
  • 2
    If the client wraps the batch in `sp_executesql` then it's a nested batch and temp tables created in it will be automatically destroyed. Only temp tables created in a "top level batch" persist until the end of the session. – David Browne - Microsoft Feb 19 '22 at 20:41
  • Thanks @DavidBrowne-Microsoft, good details. I think the `DBI`/`odbc` packages shield us from that level of execution, for better or worse. – r2evans Feb 19 '22 at 21:27