select into temporary table-CodePudding

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:

Insert Data Into Temp Table with Query from 2013
https://www.sqlshack.com/select-into-temp-table-statement-in-sql-server/ from 2021

CodePudding user response：

There are few different approaches:

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")

Use a global temp table

DBI::dbExecute(conn, "select top 5 * into ##global from sometable")
DBI::dbGetQuery(conn, "select * from ##global")

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