R- create temporary table in sql server from R dat

问题:

I know I can create a temporary table in SQL from R with, for example:

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

But I would like to create a temporary table in sql server from an R object (I have a table that has the result of previous R calculations and I need to query it against another table in SQL. I don't want to export it as txt and upload it to SQL server. It has to be a way to do it from R. I tried:

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

But I got an error message:

"42S02 208 [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'tabla'." "[RODBC] ERROR: Could not SQLExecDirect 'Select * into ##temporal from tabla '"

I also know I can create a table with sqlSave:

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

But I want to create a temporary table. How can I create a temporary table in SQL from an R object?

回答1:

Unfortunately, I don't recall sqlSave(conection, new_data, table_name, append = TRUE) ever working correctly for inserting data into existing tables (e.g. not creating new tables), so you may have to use the less efficient approach of generating the INSERT statements yourself. For example,

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)