SQL Create table from R - String data, right truncation

user_8676116 0
Viewed 2046

I am working with Microsoft SQL Azure version 12, by operating on an RStudio-server and the DBI library. I need to create multiple SQL tables from dataframes with a variable of length 4000. This can be done as

# Create dataframe
df <- data.frame("myid" = stringi::stri_rand_strings(5, 4000),
                 "mydate" = c(Sys.time(), Sys.time()-1, Sys.time()-2, Sys.time()-3, Sys.time()-4) )

# Create SQL table sschema.ttable
DBI::dbWriteTable(conn = connection,
                    name = DBI::Id(schema = "sschema", table = "ttable"),
                    value = df,
                    overwrite = TRUE)

This fails with the following error

Error in result_insert_dataframe(rs@ptr, values, batch_rows) : nanodbc/nanodbc.cpp:1617: 00000: [Microsoft][ODBC Driver 17 for SQL Server]String data, right truncation

I tried

  • Truncating variables (suboptimal)
  • Create table > alter variables to be of format VARCHAR(6000) instead of VARCHAR(255) > append dataframe. This results in the same "String data, right truncation" error.

Any solutions how to create SQL tables directly from R dataframes?

r sql-server
1 Answers

The answer is to define the variables and their desired SQL variable class with field.types as in

# Create SQL table sschema.ttable
DBI::dbWriteTable(conn = connection,
                    name = DBI::Id(schema = "sschema", table = "ttable"),
                    field.types=c(myid="varchar(6000)"),
                    value = df,
                    overwrite = TRUE)
user_86761160
Related