copy_to does not work with non-default schemas when trying to write permanent table

Question

I'm trying to use copy_to to write a table to SQL Server 2017 permanently (i.e. temporary = FALSE). It works on the default schema, but it does not work when I specify a schema other than the default schema. I get a curious error message:

Error: <SQL> 'UPDATE STATISTICS EXISTING_SCHEMA.newTblIris' nanodbc/nanodbc.cpp:1587: 42S02: [Microsoft][ODBC SQL Server Driver][SQL Server]Table 'newTblIris' does not exist.

It is curious because it is obvious the table does not exist. I am trying to create the table.

library(odbc)
library(dbplyr)
library(dplyr)
library(DBI)

con <- dbConnect(odbc(),
                 Driver = "SQL SERVER",
                 Server = "SERVER_NAME",
                 Database = "DB_NAME",
                 UID = "USER_ID",
                 PWD = "PASSWORD")

# works, can write this table to the default schema
copy_to(con, iris, "newTblIris", temporary = FALSE)

# successfully create temporary table in non-default schema
copy_to(con, iris, in_schema("EXISTING_SCHEMA", "newTblIris"))

# does not work and gives above error message
copy_to(con, iris, in_schema("EXISTING_SCHEMA", "newTblIris"), temporary = FALSE)

I have tried the "proper" way to write non-temporary tables following guidance from RStudio, https://db.rstudio.com/best-practices/schema/#write-non-temporary-tables, however when I try their method I also get an error.

dbWriteTable(con, SQL("EXISTING_SCHEMA.iris"), iris)

This gives an error Error: Can't unquote EXISTING_SCHEMA.iris

- Session info --------------------------------------------------------------------------------------------------------------------
 setting  value                       
 version  R version 3.5.2 (2018-12-20)
 os       Windows Server >= 2012 x64  
 system   x86_64, mingw32             
 ui       RStudio                     
 language (EN)                        
 collate  English_United States.1252  
 ctype    English_United States.1252  
 tz       America/New_York            
 date     2019-03-05                  

- Packages ------------------------------------------------------------------------------------------------------------------------
 package     * version date       lib source        
 assertthat    0.2.0   2017-04-11 [1] CRAN (R 3.5.2)
 backports     1.1.3   2018-12-14 [1] CRAN (R 3.5.2)
 bit           1.1-14  2018-05-29 [1] CRAN (R 3.5.2)
 bit64         0.9-7   2017-05-08 [1] CRAN (R 3.5.2)
 blob          1.1.1   2018-03-25 [1] CRAN (R 3.5.2)
 callr         3.1.1   2018-12-21 [1] CRAN (R 3.5.2)
 cli           1.0.1   2018-09-25 [1] CRAN (R 3.5.2)
 crayon        1.3.4   2017-09-16 [1] CRAN (R 3.5.2)
 DBI         * 1.0.0   2018-05-02 [1] CRAN (R 3.5.2)
 dbplyr      * 1.3.0   2019-01-09 [1] CRAN (R 3.5.2)
 desc          1.2.0   2018-05-01 [1] CRAN (R 3.5.2)
 devtools      2.0.1   2018-10-26 [1] CRAN (R 3.5.2)
 digest        0.6.18  2018-10-10 [1] CRAN (R 3.5.2)
 dplyr       * 0.8.0.1 2019-02-15 [1] CRAN (R 3.5.2)
 fs            1.2.6   2018-08-23 [1] CRAN (R 3.5.2)
 glue          1.3.0   2018-07-17 [1] CRAN (R 3.5.2)
 hms           0.4.2   2018-03-10 [1] CRAN (R 3.5.2)
 magrittr      1.5     2014-11-22 [1] CRAN (R 3.5.2)
 memoise       1.1.0   2017-04-21 [1] CRAN (R 3.5.2)
 odbc        * 1.1.6   2018-06-09 [1] CRAN (R 3.5.2)
 pillar        1.3.1   2018-12-15 [1] CRAN (R 3.5.2)
 pkgbuild      1.0.2   2018-10-16 [1] CRAN (R 3.5.2)
 pkgconfig     2.0.2   2018-08-16 [1] CRAN (R 3.5.2)
 pkgload       1.0.2   2018-10-29 [1] CRAN (R 3.5.2)
 prettyunits   1.0.2   2015-07-13 [1] CRAN (R 3.5.2)
 processx      3.2.1   2018-12-05 [1] CRAN (R 3.5.2)
 ps            1.3.0   2018-12-21 [1] CRAN (R 3.5.2)
 purrr         0.3.0   2019-01-27 [1] CRAN (R 3.5.2)
 R6            2.4.0   2019-02-14 [1] CRAN (R 3.5.2)
 Rcpp          1.0.0   2018-11-07 [1] CRAN (R 3.5.2)
 remotes       2.0.2   2018-10-30 [1] CRAN (R 3.5.2)
 rlang         0.3.1   2019-01-08 [1] CRAN (R 3.5.2)
 rprojroot     1.3-2   2018-01-03 [1] CRAN (R 3.5.2)
 sessioninfo   1.1.1   2018-11-05 [1] CRAN (R 3.5.2)
 tibble        2.0.1   2019-01-12 [1] CRAN (R 3.5.2)
 tidyselect    0.2.5   2018-10-11 [1] CRAN (R 3.5.2)
 usethis       1.4.0   2018-08-14 [1] CRAN (R 3.5.2)
 withr         2.1.2   2018-03-15 [1] CRAN (R 3.5.2)

I assume you have to create the schema beforehand, can you confirm it does exist? — Greg, Mar 07 '19 at 18:31
Correct. Schema exists, and I can confirm it does exist. ```r # create schema dbGetQuery(con, "CREATE SCHEMA EXISTING_SCHEMA") # write a table to schema to confirm it exists dbGetQuery(con , "CREATE TABLE EXISTING_SCHEMA.Region (Region_id int NOT NULL, Region_Name char(5) NOT NULL)") ``` — drizzle123, Mar 07 '19 at 18:36
the schema name is `r` or `EXISTING_SCHEMA` ? From your code above, it looks like it is the latter? — Greg, Mar 07 '19 at 18:38
I had to keep editing my comment to get the code to display properly. — drizzle123, Mar 07 '19 at 18:41
For schema support, look into [DBI::Id()](https://github.com/r-dbi/odbc/issues/91#issuecomment-371913101) which might be also available in `odbc` using latest GitHub development version (maybe now in CRAN version). — Parfait, Mar 31 '19 at 23:37
I've tried using DBI::Id(), and it works better, but there are still several annoying limitations. I posted another stackoverflow question: https://stackoverflow.com/questions/55031149/issue-writing-to-non-default-schema-when-table-does-not-have-in-name, and I submitted an issue on Github: https://github.com/r-dbi/odbc/issues/260 — drizzle123, Apr 17 '19 at 16:14

Simon.S.A. · Answer 1 · 2019-03-31T22:23:33.090

I encountered a similar problem and developed the following work around. This is the core of a function I use to write R tables to the database (the full function can be found in my dbplyr helpers repo). This cut down version assumes the table you are writing to already exists.

copy_r_to_sql = function(db_connection, schema, sql_table_name, r_table,
                         named_list_of_columns){

  # trim r table to just variables of interest
  r_table = r_table %>%
    select(names(named_list_of_columns))

  # if column type is character or date, wrap in single quotes
  # so SQL reads it as character string
  for(coln in colnames(r_table)){
    col_type = named_list_of_columns[[coln]]
    of(grepl("char", col_type) | grepl("date", col_type))
    r_table[coln] = apply(r_table[coln], 1, function(x) paste0("'", as.character(x), "'"))
  }

  # SQL
  sql_cols = paste0("([",paste0(names(named_list_of_columns), collapse = "],["), "])")
  sql_values = paste0(apply(r_table, 1, 
                            function(x) paste0("(", paste0(x, collapse = ","),")")),
                      collapse = ",\n")

  my_sql = build_sql(con = db_connection,
                     "INSERT INTO ", sql(schema), ".",sql(sql_table_name),"\n",
                     sql(sql_cols), "\n",
                     "VALUES ", sql(sql_values),";")

  result = dbExecute(db_connection, as.character(my_sql))
}

The function is designed to write the SQL text that would add the specified rows to your table. To understand what the function does you can replace the last line dbExecute with a print statement.

By way of an example function call:

named_list_of_columns = list(Sepal.Length = "[float](5,1) NOT NULL",
                             Sepal.Width = "[float](5,1) NOT NULL",
                             Species = "[varchar](15) NOT NULL")

copy_r_to_sql(con, "DB_NAME.SCHEMA", "newTblIris", iris, named_list_of_columns)

I have not tried using this for large tables. If you needed to load a large number of rows, I recommend creating a new table and appending to it in batches.

Thanks for sharing this. Could you provide a sample function call so I can get a better idea of what all the input parameters look like, so to speak? — drizzle123, Mar 29 '19 at 16:25
Interesting. It seems as if you literally turning the R data.frame into a string which could then be understood in SQL. Very cool. I posted similar (albeit different question) here (https://stackoverflow.com/questions/55106088/sql-function-not-working-when-trying-to-write-table-to-non-default-schema), and you may be interested in the answer. — drizzle123, Apr 17 '19 at 16:20

copy_to does not work with non-default schemas when trying to write permanent table

1 Answers1