How to create SQL Server table from dplyr pipeline

Question

Due to a bug in dbplyr, copy_to and compute are currently not working for SQL Server connections.

connStr <- "driver=ODBC Driver 13 for SQL Server;server=localhost;..."
db <- DBI::dbConnect(odbc::odbc(), .connection_string=connStr)

copy_to(db, mtcars)
#Error: <SQL> 'CREATE TEMPORARY TABLE "mtcars" (
#  "row_names" varchar(255),
#  "mpg" FLOAT,
#  ...
#  nanodbc/nanodbc.cpp:1587: 42000: [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Unknown object type 'TEMPORARY' used in a CREATE, DROP, or ALTER statement. 

# use raw DBI functionality to create table
DBI::dbWriteTable(db, "mtcars", mtcars)

qry <- tbl(db, "mtcars") %>% group_by(am) %>% summarise(m=mean(mpg))

compute(qry)
#Error: <SQL> 'CREATE TEMPORARY TABLE "isrxofsskr" AS SELECT "am" AS "am", "m" #AS "m"
#FROM (SELECT "am", AVG("mpg") AS "m"
#FROM "mtcars"
#GROUP BY "am") "htrkkxabrn"'
#  nanodbc/nanodbc.cpp:1587: 42000: [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Unknown object type 'TEMPORARY' used in a CREATE, DROP, or ALTER statement. 

There is an active PR on the dbplyr repo that solves this problem, but no indication of when this will be merged (or when it will reach CRAN). In the meantime, how would I create a table from the query, without reading the data into R?

Answer 1

It turns out that the PR on the dbplyr repo is glitched anyway, and will pull the entire table into memory before writing it back.

Fixing the problem requires creating a couple of MSSQL-specific methods for dbplyr generics. These are listed below. I've also posted them to the dbplyr repo so (assuming they work) they should hopefully be merged before too long.

#' @export
`db_compute.Microsoft SQL Server` <- function(con, table, sql, temporary=TRUE,
     unique_indexes=list(), indexes=list(), ...)
{
    # check that name has prefixed '##' if temporary
    if(temporary && substr(table, 1, 1) != "#")
        table <- paste0("##", table)

    if(!is.list(indexes))
        indexes <- as.list(indexes)

    if(!is.list(unique_indexes))
        unique_indexes <- as.list(unique_indexes)

    db_save_query(con, sql, table, temporary=temporary)
    db_create_indexes(con, table, unique_indexes, unique=TRUE)
    db_create_indexes(con, table, indexes, unique=FALSE)
    table
}


#' @export
`db_save_query.Microsoft SQL Server` <- function(con, sql, name, temporary=TRUE, ...)
{
    # check that name has prefixed '##' if temporary
    if(temporary && substr(name, 1, 1) != "#")
        name <- paste0("##", name)

    tt_sql <- build_sql("SELECT * INTO ", ident_q(name),
                        " FROM (", sql, ") ", ident_q(name), con=con)

    dbExecute(con, tt_sql)
    name
}

Note: may not be Bobby Tables-resistant. Testing is advised.