R-从R数据框在SQL Server中创建临时表

Question

我知道我可以用以下代码在R中创建一个临时表：

require(RODBC)

X<- odbcDriverConnect('driver={SQL Server};
    server=s001000;database=X1;trusted_connection=true')

sqlQuery(X, "create table #temptable (test int)" )
sqlQuery(X,  "insert into #temptable(test) values(201508)")
doesItWork <- sqlQuery(X, "select * from #temptable")

但是我想从R对象中在SQL Server中创建一个临时表格 (我有一个包含以前R计算结果的表格，我需要将它与SQL中的另一个表格进行查询。我不想将它导出为txt文件并上传到SQL Server。必须有一种从R中执行此操作的方法。我尝试过：

tabla<-data.frame(per=c(201508,201510))
sqlQuery(X, "Select * into ##temporal from tabla")

但是我收到了一个错误消息：

"42S02 208 [Microsoft][ODBC SQL Server Driver][SQL Server]对象名无效 'tabla'。" "[RODBC] 错误：无法 SQLExecDirect 'Select * into ##temporal from tabla '"

我也知道可以使用sqlSave创建一个表：

sqlSave(X, tabla, rownames=FALSE,safer=FALSE)

但是我想创建一个临时表。如何从R对象在SQL中创建一个临时表？

- GabyLP

---

我认为你不能像使用 sqldf 一样通过 sqlQuery 在查询中引用 R 会话 (tabla) 中的对象。尝试使用 sqlSave 或 sqlUpdate 进行插入操作。 - nrussell

Answer 1

很遗憾，我不记得sqlSave(conection, new_data, table_name, append = TRUE)曾正确地将数据插入到现有表（例如不创建新表）中，因此您可能需要使用效率较低的方法自己生成INSERT语句。例如，

con <- odbcConnect(...)

query <- "
SET NOCOUNT ON;

IF ( OBJECT_ID('tempdb..##tmp_table') IS NOT NULL )
    DROP TABLE ##tmp_table;
CREATE TABLE ##tmp_table
    (
     [ID] INT IDENTITY(1, 1)
    ,[Value] DECIMAL(9, 2)
    );

SET NOCOUNT OFF;

SELECT  1;
"
sqlQuery(con, gsub("\\s|\\t", " ", query))


df <- data.frame(Value = round(rnorm(5), 2))

update_query <- paste0(
    "SET NOCOUNT ON; INSERT INTO ##tmp_table ([Value]) VALUES ",
    paste0(sprintf("(%.2f)", df$Value), collapse = ", "),
    " SET NOCOUNT OFF; SELECT * FROM ##tmp_table;"
)

sqlQuery(con, update_query)
#   ID Value
# 1  1  0.79
# 2  2 -2.23
# 3  3  0.13
# 4  4  0.07
# 5  5  0.50

#sqlQuery(con, "DROP TABLE ##tmp_table;")
#odbcClose(con) 

- nrussell