Upload to a SQL Server database using odbc

The function performs some extra configuration to improve robustness.

upload_sqls_odbc(
  d,
  schema_name,
  table_name,
  dsn_name,
  clear_table = FALSE,
  create_table = FALSE,
  convert_logical_to_integer = FALSE,
  timezone = "UTC",
  timezone_out = "UTC",
  transaction = FALSE,
  verbose = TRUE,
  verbose_detail = FALSE
)

Arguments

d

Dataset to be uploaded to the dataset. The object must inherit from data.frame.

schema_name

Name of the database destination table.

table_name

Name of the database destination table.

dsn_name

Name of the locally-defined DSN passed to DBI::dbConnect().

clear_table

If TRUE, calls deletes or truncates all records before writing to the table.

create_table

If the table structure has not yet been defined in the database, it will be created if create_table is TRUE.

convert_logical_to_integer

Convert all logical columns to integer. This helps the database store the values as bits.

timezone

The server time zone. Passed to DBI::dbConnect().

timezone_out

The time zone returned to R. Passed to DBI::dbConnect(). See https://www.tidyverse.org/blog/2019/12/odbc-1-2-0/.

transaction

Should the clear and upload steps be wrapped in a rollback transaction?

verbose

Display messages about the status of an upload.

verbose_detail

Display a message about the connection's low-level details.

Value

A boolean value that was returned by DBI::dbWriteTable().

Details

If transaction is TRUE and the upload fails, the table is rolled back to the state before function was called. This includes rolling back the (optional) clearing of records, and uploading the new records. Decide if it's more robust to rollback to the previous state, or if it's better to leave the table in the incomplete state. The latter is helpful diagnosing which record caused the write to fail; look at the last successful record contained in the database

Examples

if (FALSE) { # \dontrun{
requireNamespace("OuhscMunge")

OuhscMunge::upload_sqls_odbc(
  d                          = ds_client,          # Some data.frame that exists in RAM
  schema_name                = "dbo",
  table_name                 = "tbl_client",
  dsn_name                   = "miechv_eval",
  create_table               = FALSE,
  clear_table                = TRUE,
  transaction                = TRUE,
  verbose                    = TRUE,
  convert_logical_to_integer = TRUE,
  timezone                   = "America/Chicago",
  timezone_out               = "America/Chicago"
)
} # }