选择并创建临时表。

Question

选择并创建临时表。

3

我相信我应该能够执行select * into #temptable from othertable（其中#temptable之前不存在），但它不起作用。假设othertable存在并具有有效数据，并且#sometemp不存在，

# 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.

非临时版本无误。

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

系统信息：

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

在Win11和Ubuntu上进行了测试。R-4.1.2，DBI-1.1.2，odbc-1.3.3。

我看到一些评论表明"select into ..."不适用于临时表，但我也看到有几个教程演示它可以工作（对他们而言）。

背景：这是一个通用的访问函数，用于插入数据：我插入一个临时表，执行upsert，然后删除临时表。我可以使用非临时表，但我认为在合理的情况下使用临时表有有效的理由，并且我想了解为什么这不能或不应按预期工作。除了切换临时表之外，我还可以尝试通过编程方式重新构建othertable的结构，但这容易出现某些列类型的解释错误。我不能只插入一个临时表，因为有时数据类型映射不完美（例如当我应该使用nvarchar(max)和/或当新列是不确定的时候，因为它们全是NA）。

相关链接：

使用查询将数据插入临时表，出自2013年
https://www.sqlshack.com/select-into-temp-table-statement-in-sql-server/，出自2021年

- r2evans

我也看过几个教程演示它的工作原理。但要注意不要依赖于几年前（十多年前！）的帖子。简而言之，使用临时表需要深入了解范围以及它们何时被数据库引擎删除的方式。反过来，您需要知道数据访问层在用于多个语句时如何处理连接。 - SMor

链接: 当然，已经编辑到问题中了，我认为2021年足够新（而且我推断sqlshack的可信度）。您知道在过去十年中SQL Server是否有任何重大变化，其中他们曾经支持某些内容并撤销了该支持吗？范围: 您是指#是本地的，而##是“全局本地”的（对于用户来说）吗？我知道它们何时关闭，这与连接恒定，不间断无关。 多个语句: 遗憾的是，R的DBI/odbc不支持多个语句。您有使用R的经验吗？ - r2evans

1

我也发现这个不一致。您可以使用 tbl(conn, sql("select top 1 * from othertable")) %>% compute()，它将在数据库上创建一个临时表，其中包含othertable的第一行。如果您将该调用分配给一个对象，比如说 tt，您可以像往常一样使用它。您还可以访问该对象的名称（tt$ops$x$x），它看起来类似于 #dbplyr_005。 - langtang

1

我可以通过以下方法让全局临时表起作用：DBI::dbExecute(mycon, "select top 5 * into ##mytemp from sometable")，然后是DBI::dbGetQuery(mycon, "select * from ##mytemp")。这有帮助吗？ - langtang

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- langtang · Accepted Answer

有几种不同的方法：

在您的 DBI::dbExecute 语句中使用 immediate 参数

DBI::dbExecute(conn, "select top 5 * into #local from sometable", immediate=TRUE)
DBI::dbGetQuery(conn, "select * from #local")

使用全局临时表

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

使用dplyr/dbplyr

tt = tbl(conn, sql("select top 5 * from sometable")) %>% compute()
tt

另请参见：https://github.com/r-dbi/odbc/issues/127