Search code examples
sqlsql-serverrrodbc

R and MSSQL - communication with temp tables (table not found on channel)

I'm trying to update global temp table using RODBC in a following way:

library(RODBC)
channel <- odbcConnect("RDataSource", uid = "user", pwd = "password")    
query <- "select * from ##TempTable"
table_data <- sqlQuery(channel, query)
# data frame creation
sqlUpdate(channel, data_frame, index = "id", verbose = TRUE, tablename = "##TempTable")

Select query executes well, but sqlUpdate is failed with error message: "Error in odbcTableExists(channel, tablename) : ‘##TempTable’: table not found on channel"

I suppose that the reason of this error may be connected with using of '#' in the name of temp tables.

UPD: I'm getting the same error with sqlSave function. This error occures only when I'm creating temp table, everything is ok with usual SQL tables. Global temp table is creating before the calling of R code.

So, is there any way to communicate with temp tables in MSSQL database using R functions such as sqlSave() and sqlUpdate()?

Solution

  • I finally found that the cause of this error was in the settings of ODBC Data Source. It seems that communication with temp tables using R functions such as sqlSave or sqlUpdate requires the default database in ODBC data source to be set to 'tempdb'. So, now I'm able to use sqlSave() function to insert values in temp table. This function in fact has much better performance than using sqlQuery function with the direct 'INSERT' query as a parameter.