copy_dm_to()
takes a dplyr::src_dbi object or a DBI::DBIConnection
object as its first argument
and a dm
object as its second argument.
The latter is copied to the former.
The default is to create temporary tables, set temporary = FALSE
to create permanent tables.
Unless set_key_constraints
is FALSE
, primary key constraints are set on all databases,
and in addition foreign key constraints are set on MSSQL and Postgres databases.
Usage
copy_dm_to(
dest,
dm,
...,
types = NULL,
overwrite = NULL,
indexes = NULL,
unique_indexes = NULL,
set_key_constraints = TRUE,
unique_table_names = NULL,
table_names = NULL,
temporary = TRUE,
schema = NULL,
progress = NA,
copy_to = NULL
)
Arguments
- dest
An object of class
"src"
or"DBIConnection"
.- dm
A
dm
object.- ...
Passed on to
dplyr::copy_to()
or to the function specified by thecopy_to
argument.- overwrite, types, indexes, unique_indexes
Must remain
NULL
.- set_key_constraints
If
TRUE
will mirrordm
primary and foreign key constraints on a database and create unique indexes. Set toFALSE
if your data model currently does not satisfy primary or foreign key constraints.- unique_table_names
Deprecated.
- table_names
Desired names for the tables on
dest
; the names within thedm
remain unchanged. Can beNULL
, a named character vector, a function or a one-sided formula.If left
NULL
(default), the names will be determined automatically depending on thetemporary
argument:temporary = TRUE
(default): unique table names based on the names of the tables in thedm
are created.temporary = FALSE
: the table names in thedm
are used as names for the tables ondest
.
If a function or one-sided formula,
table_names
is converted to a function usingrlang::as_function()
. This function is called with the unquoted table names of thedm
object as the only argument. The output of this function is processed byDBI::dbQuoteIdentifier()
, that result should be a vector of identifiers of the same length as the original table names.Use a variant of
table_names = ~ DBI::SQL(paste0("schema_name", ".", .x))
to specify the same schema for all tables. Usetable_names = identity
withtemporary = TRUE
to avoid giving temporary tables unique names.If a named character vector, the names of this vector need to correspond to the table names in the
dm
, and its values are the desired names ondest
. The value is processed byDBI::dbQuoteIdentifier()
, that result should be a vector of identifiers of the same length as the original table names.Use qualified names corresponding to your database's syntax to specify e.g. database and schema for your tables.
- temporary
If
TRUE
, only temporary tables will be created. These tables will vanish when disconnecting from the database.- schema
Name of schema to copy the
dm
to. Ifschema
is provided, an error will be thrown iftemporary = FALSE
ortable_names
is notNULL
.Not all DBMS are supported.
- progress
Whether to display a progress bar, if
NA
(the default) hide in non-interactive mode, show in interactive mode. Requires the 'progress' package.- copy_to
By default,
dplyr::copy_to()
is called to upload the individual tables to the target data source. This argument allows overriding the standard behavior in cases when the default does not work as expected, such as spatial data frames or other tables with special data types. If notNULL
, this argument is processed withrlang::as_function()
.
Details
No tables will be overwritten; passing overwrite = TRUE
to the function will give an error.
Types are determined separately for each table, setting the types
argument will
also throw an error.
The arguments are included in the signature to avoid passing them via the
...
ellipsis.
Examples
con <- DBI::dbConnect(RSQLite::SQLite())
# Copy to temporary tables, unique table names by default:
temp_dm <- copy_dm_to(
con,
dm_nycflights13(),
set_key_constraints = FALSE
)
# Persist, explicitly specify table names:
persistent_dm <- copy_dm_to(
con,
dm_nycflights13(),
temporary = FALSE,
table_names = ~ paste0("flights_", .x)
)
dbplyr::remote_name(persistent_dm$planes)
#> <IDENT> flights_planes
DBI::dbDisconnect(con)